sp_addlinkedsrvlogin access for non-admin user

Question

sp_addlinkedsrvlogin access for non-admin user

Andy_UK

SSC Enthusiast

Points: 186
More actions
October 25, 2012 at 5:36 am

#283559

I am trying to create an application which creates new linked server user mappings based on input from an ASP.net application.
Everything works fine using my account, as a SQL admin I would expect nothing less, but when trying to use anoter users account, via a group mapping, I cannot seem to create a new user mapping.
I have tried:
1) Granting the user group the 'Alter Any Login' priviledge and granting them execute permissions against the sp_addlinkedsrvlogin stored proc in the master database.
2) Executing sp_addlinkedsrvlogin as different user using 'Execute as Login = [myaccount]' with impersonation granted to the user group. (Not an ideal production solution)
Is there anything else I can try?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

Andy Let me know if this helps at all.

What i usually do in that specific situation is to create a sql user, without login on the linked server that has the expected permissions...for me that's usually readonly rights.

The advantage of the WITHOUT LOGIN is that noone can use it to login, it's only accessible via things like a linked server or EXECUTE AS wehn you are a sysadmin.

That's the users I use to map specific windows users or groups to use when accessing the linked server.

for example, two users , created on the linked server itself.

CREATE USER READ_ONLY WITHOUT LOGIN;

EXEC sp_addrolemember 'db_datareader','READ_ONLY' ;

CREATE USER LIMITED_USER WITHOUT LOGIN;

EXEC sp_addrolemember 'db_datareader','LIMITED_USER' ;

EXEC sp_addrolemember 'db_datawriter','LIMITED_USER' ;

GRANT EXECUTE TO LIMITED_USER ;

after that, I setup the linked server to use those new users.... i simply map each of the groups or users (whether windows or SQL) to which specific user to impersonate. I like that a lot better than impersonating a real user that might already exist.

That way, if they are not on the list, they don't get access.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Andy_UK SSC Enthusiast Points: 186 More actions · Answer 2

Lowell,

Thanks for replying so promptly.

The issue I have is that the server I am linking to is not a SQL server but an IBM iSeries.

I need to pass through the permissions for each user as I cannot have a single linked server login as each user has different priviledges on the iSeries.

Andy

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 3

Instead of using EXECUTE AS LOGIN = in the proc that creates the Linked Server and granting impersonation rights to your user base for that LOGIN you can try using a certificate to sign the stored procedure. This would require server-level permissions so it will be a certificate in the master database, but it should get you where you want to go:

1. low priv users can cerate a Linked Server via a stored proc specially crafted only to allow them to do what you want to allow

2. users cannot elevate their own permissions using EXECUTE AS within a SQL Server session

Tutorial: Signing Stored Procedures with a Certificate

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato