Login mapping only works for the first query in a window

Question

I have a really odd login mapping error for one DB that I can't seem to figure out.

When running a simple select statement while impersonating a login (A) on DB(1), it appears to work fine. However it only works the first time the query is run on each query window.

After that I repeatedly get an error stating

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

Opening a new query window and running exactly the same statement while again impersonation login (A) works every time - but again only the first time it is run before the same mapping error returns. This is also repeatable on the same DB(1) for any other logins on the instance that are not sysadmin.

Nothing is being changed on the mapping settings in-between running the queries. I've even gone through and checked no settings have dropped off. No other DBs on the instance experience this issue. The logins use Windows Authentication. The required 'User mapping' has been set at instance level with a tick against the DB, and the login is designated as DB_Owner. This has been used to create the DB login.

The login only has the server role 'Public' to avoid them accessing other DBs. SysAdmins have no issues on this DB. This DB(1) has previously experienced issues with login mappings randomly dropping off. Re-creating the logins has always solved any issues. Obviously this shouldn't be required but I've found no reason why this happens either. I've re-created this login several times but on this ocassion it is not solving the issue.

To me it's a pretty standard setup and seems to work fine everywhere else, but I can't see why this DB is saying no login-mapping exists, especially when it works the first time on each query window.

For security reasons I'd rather not give the account sysadmin rights just to resolve this. I've never seen anything like this before though and can't find any google results suggesting this is a known bug.

Anyone seen this before and got any ideas on the cause?

I'm Using SQL Server 2008.

Answer 1

Considering that you are using the following SQL:

SELECT * FROM view EXECUTE AS LOGIN = 'loginname'

This is actually 2 different statements:

SELECT * FROM view;
EXECUTE AS LOGIN = 'loginname';

The SELECT statement doesn't have a clause to impersonate, you have to impersonate before issuing the SELECT statement and the impersonation remains on the current session until you execute a REVERT. So the proper SQL should have been:

EXECUTE AS LOGIN = 'loginname';
SELECT * FROM view;
REVERT; -- (if you want to revert to your original login)

As you mentioned in a comment, the error message was being generated by the view that you were querying, as it was using a linked server that wasn't correctly configured with a login against the remote server in which the impersonated login had access. If the current login is sysadmin, the linked server won't require a remote login-mapping mechanic (this can be configured), so the query always succeeded the first time.