Permissions for linked server

Hello, I have two servers server 'A' and server 'B'. server A has logins which are dbo i.e, the logins are just the database name so when some one login, they use username as databasename and password and when they login they can only see that database, other databases on the server are hidden and the users given dbo permission. I have created a linked server between server A and server B. i want to restrict the logins to use that linked server to change the data. The users can select data from server 'B' but should not change the data. How can i restrict the users to only SELECT??

more ▼

asked Jul 30, 2014 at 09:16 PM in Default

avatar image

311 13 20 26

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The simplest solution would be to create a new login on Server B for the linked server connection.

Use that new login from Server B to define the linked server connection authentication that you will create on Server A.

Then on Server B, add that login to have access to the required databases. Grant select permissions where appropriate (at the table level within the database or at the database level via db_datareader).

The linked server will now connect to Server B with only the credentials of that new Login you created and the user will only be able to select.

more ▼

answered Aug 01, 2014 at 01:04 AM

avatar image

5.5k 11 13 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 30, 2014 at 09:16 PM

Seen: 1015 times

Last Updated: Aug 01, 2014 at 01:04 AM

Copyright 2018 Redgate Software. Privacy Policy