CODEWITHSUNDEEP
sql-server
Sohail Shahzad
Sohail Shahzad

Reputation: 369

Linked server permission for specific user in SQL Server

Is there any way that I change permission for linked server that user could not see linked server objects?

Actually I want to give access for linked server to specific user other should not show linked server objects.

Upvotes: 1

Views: 31998

Answers (1)

sepupic
sepupic

Reputation: 8687

You can limit your linked server to being visible only to one or some users.

If your linked server is already created and mapping is made, this will help you:

When you create a linked or remote server, SQL Server creates a default login mapping to the public server role. This means that by default, all logins can view all linked and remote servers. To restrict visibility to these servers, remove the default login mapping by executing sp_droplinkedsrvlogin and specifying NULL for the locallogin parameter.

If the default login mapping is deleted, only users that have been explicitly added as a linked login or remote login can view the linked or remote servers for which they have a login.

sys.servers (Transact-SQL)

If you set a new server, go to security tab, choose connections as "not be made" and add only a login that is supposed to reach linked server:

enter image description here

In my case I used impersonation as the same exact login exists on linked server but you can use any other mapping: uncheck "impersonate" and fill up "remote user" in case you want to map your local login to different login on linked server.

Upvotes: 2

Related Questions