Microsoft, SQL Server, 2005, Linked Server Roles

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.

Thanks!
knaren_1975Asked:
Who is Participating?
 
slam69Connect With a Mentor Commented:
well you would need to be a sysadmin to create a li9nked server.

Consider coding into yoru stored procedure an execute as line though to enable you to run teh procdure without elevating the Yuser privilidges

http://msdn.microsoft.com/en-us/library/ms178106.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.