Sql-server – Permission to create linked server

sql serversql-server-2008sql-server-2008-r2

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.

Best Answer

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

Related Question