Solved

Microsoft, SQL Server, 2005, Linked Server Roles

Posted on 2008-09-29
3
502 Views
Last Modified: 2012-05-05
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
Comment
Question by:knaren_1975
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 25

Accepted Solution

by:
slam69 earned 500 total points
ID: 22595372
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question