Microsoft, SQL Server, 2005, Linked Server Roles
Posted on 2008-09-29
Here is a description of my problem,
1> I have a login Xuser, which is having sysadmin privileges.
Using this i create a procedure Xcreate, which in turn calls sp_addlinkedserver to create a linked server.
2> I give execute grants on the procedure xcreate to a normal user Yuser, who is just a member of Public role.
3> I log in to the server using Yuser, and execute the procedure Xcreate.
It throws an error that i do not have privileges to create a linked server.
4> This, is a peculiar problem to a novice like me.
Ideally Xcreate should execute under the privileges of its owner, which is Xuser (sysadmin).
can you please help me out? I need to know which other system role should Yuser be mapped to so that yuser can create linked servers.
I strictly do not want to make yuser a sysadmin, and limit yuser's privileges.