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??
asked Jul 30, 2014 at 09:16 PM in Default
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.
answered Aug 01, 2014 at 01:04 AM