Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 6 April 2016

To Create Linked Server from User What types of Permissions Need in SQL Server

To Create Linked Server from User What types of Permissions Need in SQL Server

sp_addlinkedserver

Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 

Permissions


User/Logins Requires ALTER ANY LINKED SERVER permission.
sp_addlinkedserver cannot be executed within a user-defined transaction.
you can see sample login name TLS have basic permissions it did n't have special permissions

When TLS login have tried to create Linked server through T-SQL it throws error see below








When TLS login have tried to create Linked server through GUI  it throws error see below 


So TLS login have ALTER ANY LINKED SERVER permission.
We can assign through T-SQL See below


After TLS Login have ALTER ANY LINKED SERVER permission.
TLS login can able to create linked server see below


That's it