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'.
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!!!!
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.