Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- sys.dm_server_services - How to check if SQL Server service is running or not?
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
- sqlserver-dba.com switching from feedburner to follow.it
- Taking transaction log backup and full back at the same time
SQL Server Linked Servers and User permissions
10 May,2011 by Tom Collins
A developer requested erroneously,sysadmin Server Role to create a SQL Server 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;GOUSE 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’
Read More
Linked Server migration from sql server 2000 to 2005 - SQL Server ...
SQL Server - How to setup ODBC for IBM AS/400 - SQL Server DBA
Author: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |