snippetsqlMinor
Permission to create linked server
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:
But didn't worked to create linked server.
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 loginBut didn't worked to create linked server.
Solution
There is no requirement for
Assigning the permissions ,
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
Code examples: Assigning permissions
Link: http://www.sqlserver-dba.com/2011/05/sql-server-linked-servers-and-user-permissions.html
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_addlinkedsrvloginCode 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.