3

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.

  • What error message did you get? – Kevin3NF Jul 18 '17 at 15:12
4

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

  • I understood user can create linked server using T-SQL EXEC sp_addlinkedserverr ..What if he needs to create using GUI? – user112359 Jul 18 '17 at 15:57
  • Check this link: msdn.microsoft.com/en-us/library/aa560998.aspx – CR241 Jul 18 '17 at 16:18
  • @user112359 then sysadmin is required, per the docs that have been posted. Or he requests that the DBA do it, which is the right process anyway. – Kevin3NF Jul 18 '17 at 16:47
2

From Microsoft Docs: https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine

Security Permissions

When using Transact-SQL statements, requires ALTER ANY LINKED SERVER permission on the server or membership in the setupadmin fixed server role. When using Management Studio requires CONTROL SERVER permission or membership in the sysadmin fixed server role.

With that in mind, I'd recommend reviewing what their needs are, so that you are in compliance with any auditors, and the linked server isn't dangerous in any way.

If the linked server creation didn't work, it may be either:

  • A networking issue where the servers aren't able to talk.
  • A user issue where the linked server user isn't able to login successfully.

Any particular errors you can share?

2

The sp_addlinkedserver statement requires the ALTER ANY LINKED SERVER permission.

New Linked Server GUI requires sysadmin...the sp does not. Just verified with a new SQL login that has pretty much nothing but ALTER ANY LINKED SERVER.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.