CODEWITHSUNDEEP
sql-server
user2502961
user2502961

Reputation: 157

How to allow external application to access only one table?

A third party application will be accessing our application database to read appointment records from a table in my to send remainders to customers.

I was planning to create a view to get all the information they needed and have them access that view. So, is there a way I can restrict third party application to just access the view ? Or is there a better way to do this?

Also they will be sending me a confirmation which I need to write back to the appointment record. So, can I have a stored procedure to writes back that information and have them access only that stored procedure?

I never came across this situation and would like to get some advice from experts here.

Upvotes: 1

Views: 557

Answers (2)

ADyson
ADyson

Reputation: 62009

1) Within the database that contains your view, create an Application Role specifically for your 3rd-party app. Give the role SELECT permission to that view, and EXECUTE permission on the stored procedure.

2) On the database server, create a login specifically for use by your 3rd-party application. Give that login the role you just created in step 1, and nothing else.

You can do all of this using the SQL Server Management Studio GUI quite easily. There are plenty of guides online if you need a step by step walk-through of the different actions.

Upvotes: 1

Tudor Constantin
Tudor Constantin

Reputation: 26861

While you can create a separate user and restrict its access to a single table I would definitely build an API endpoint for them to use.

Upvotes: 1

Related Questions