Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

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!
0
knaren_1975
Asked:
knaren_1975
1 Solution
 
slam69Commented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now