SQL Version 2005. A sql user can't read across database even though read access on specific tables ( securables) is granted in the other database. Got "security context" error. This happens when it is called from the application issuing a query that looks up data from the other database but security is granted on those tables as I mentioned. We have tried everything: deleting the db user, adding it back, ran sp_change_users_login to find any descrepancy between syslogin and sysuser in db. No problem found. Strange thing is immitating the application, if "execute as login='xxxxx'" then the cross db query works, if "execute as user='xxxx'" then failed and received "The principle xxxx is not able to access the database yyyy under the current security context".  Any help would be most appreciated.

Did you try to log on with this user and check if this user is able to see the tables from SSMS?

Hi

They need a login in the database.  If the login does exist in the database check that db_denydatareader is not selected.

>> if "execute as login='xxxxx'" then the cross db query works, if "execute as user='xxxx'"

So did you checked that user has login associated with it. What rights does the user have and what rights the login have

if "execute as login='xxxxx'" then the cross db query works, if "execute as user='xxxx'" then failed

A user is a database level principal = only related to the current database, while a login is a server level principal.

To get a cross database access working with a "user" impersonation the database must be set to trustworthy; see Guidelines for using the TRUSTWORTHY database setting in SQL Serv

Thank you for your replies. I have tried removing everything: syslogin, and sysusers and adding back in; made sure the user in the remote db has read permission on that table. Still same Security Context Error. To answer earlier reply, the user is in both dbs, else "execute as login=xxxx; then run query to include remote table" will not work. It only gets the security context error when "execute as user=xxxx".  Please give me suggestions how to solve this, the user app needs to go production and can't happen w/o this fixed. Thank you very much.

 

Anyone with fresh ideas ? I have gone the length of querying the SID and see if it match to syslogin in both based and remote db; they are same. I have nowhere to go on this. Please help.