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.
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
From Microsoft Docs: https://learn.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:
Any particular errors you can share?
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.
