Solved

Microsoft, SQL Server, 2005, Linked Server Roles

Posted on 2008-09-29
3
498 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
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to set audit trial on sql server 4 45
sql query help 7 96
PERFORMANCE OF SQL QUERY 13 66
ms sql + top 1 for each customer 3 44
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now