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.