SQL 2005, OPENQUERY and security context switching

SQL Server Security

Bobo1234


Hi,

I need to allow only a specific group of users to use a linked server. Rather than granting each user access in the linked server I create and give access to a local SQL login "link_user" and then grant the group impersonate to "link_user". The folllowing statements then work fine:

EXECUTE AS LOGIN = 'link_user'

SELECT * FROM OPENQUERY(linked_server, 'SELECT * FROM ...)

But when I use the exact same statements in a stored procedure I get the folllowing error:

Msg 7416, Level 16, State 1, Procedure linktest2, Line 5

Access to the remote server is denied because no login-mapping exists.

I need to be able to work with the result set which is why I use OPENQUERY rather than EXECUTE... AS... AT which works fine also in stored procedures!

Is there another workaround then to create all users in the linked server security

Rgds

Bertrand

Top

Re: SQL 2005, OPENQUERY and security context switching

Raul Garcia - MS


I recently answered a similar question in this forum thread. I suspect that the reason why it is failing is the same. Can you please try separating the EXECUTE AS LOGIN from the SELECT statement

EXECUTE AS LOGIN = 'link_user'

go

SELECT * FROM OPENQUERY(linked_server, 'SELECT * FROM ...)

go

Please let us know if this was indeed the same scenario.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine






Top