question

Vineet avatar image
Vineet asked

Linked Servers

Just tried creating linked server and noticed after giving setupadmin and alter any linked server privilege it is still not working. But when I give login sysadmin privilege it works. Any ideas? The error i get is below: Msg 229, Level 14, State 5, Procedure sp_addlinkedserver, Line 1 The EXECUTE permission was denied on the object 'sp_addlinkedserver', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_addlinkedsrvlogin, Line 1 The EXECUTE permission was denied on the object 'sp_addlinkedsrvlogin', database 'mssqlsystemresource', schema 'sys'.
linked-serverlinked
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
Why would you give setupadmin with on a linked server? You need to have this role on the server where you've created the linked server. On the linked server you need read/write/update rights on a database(s) Giving somebody sysadmin is promoting him to God :) No rules!!!!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

FYI MSSSQLSystemResource is the hidden system database (starting at SQL 2K5), see http://msdn.microsoft.com/en-us/library/ms190940(v=SQL.90).aspx
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
For adding the linked server through sp_addlinkedserver, you should have ALTER ANY LINKED SERVER permission. Whereas, for creating or updating a mapping between logins on the local instance of SQL Server and remote logins on the linked server, you should have the ALTER ANY LOGIN permissions. This is where the execution of "sp_addlinkedsrvlogin" is unsuccessful. Hope it clears the ambiguity. Moreover, you do not have to give sysadmin rights to create a linked server.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.