SQL Server Linked Servers and User permissions

10 May,2011 by Jack Vamvas

A developer requested erroneously,sysadmin Server Role to create a SQL Server Linked Server. There is no requirement for sysadmin to create a SQL Server Linked Server .

Assigning  the permissions ,ALTER ANY LINKED SERVER and ALTER ANY LOGIN to a login account  , allows the actions to complete.

Create a Linked Server with master.dbo.sp_addlinkedserver.

ALTER ANY LINKED SERVER permissions are required

 Drop a Linked Server with master.dbo.sp_dropserver.
ALTER ANY LINKED SERVER permissions are required

Create the mapping of a local login with a remote server with master.dbo.sp_addlinkedsrvlogin

Code examples :

Assigning permissions


USE master;GRANT ALTER ANY LINKED SERVER TO MYLOGINACCT;GOUSE master;GRANT ALTER ANY LOGIN SERVER TO MYLOGINACCT; --An example of adding a Linked Server EXEC sp_addlinkedserver      @server='Server1',    @srvproduct='',   @provider='SQLNCLI',    @datasrc='Server1\instance1'---An example of dropping a Linked Server EXEC master.dbo.sp_dropserver ‘Server1’

 Read More

 Linked Server migration from sql server 2000 to 2005 - SQL Server ...

SQL Server - How to setup ODBC for IBM AS/400 - SQL Server DBA

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer