HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Permission to create linked server

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
serverpermissionlinkedcreate

Problem

I have a user who has DB_Owner permission on a particular database.
Now, that user needs to create a linked server for himself.
what level of permission do I need to grant to that user at the server level for him to create Linked Server?I don't want to grant him sysadmin access.
I gave following access:

Grant control server to login
grant alter any linked server to login


But didn't worked to create linked server.

Solution

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;
GO
USE 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’


Link: http://www.sqlserver-dba.com/2011/05/sql-server-linked-servers-and-user-permissions.html

Code Snippets

USE master;
GRANT ALTER ANY LINKED SERVER TO MYLOGINACCT;
GO
USE 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’

Context

StackExchange Database Administrators Q#180137, answer score: 5

Revisions (0)

No revisions yet.