Anyone have any specifics I can check for?
We have a DB(call it DB2) where 3rd parties sign in with views pointing to other databases(call it DB1) through synonyms. We have a SuperUser that has db owner access to both databases, we hide the user and use it in Stored procedures
ALTER PROCEDURE [dbo].[StoredProcedure]
WITH EXECUTE AS 'SuperUser'
In the Procedure we have a select statement that gets data from DB1 using synonyms
The I grant the 3rd Party user execute permissions on the Procedure
When this user or even the sysadmin tries executing the procedure we get
The server principal is not able to access the database under the current security context
But If I login as the Superuser on DB2 and run the select in the procedure we get results
Further a sysadmin users owns both databases and all database objects. We even tried DB chaining, switching it on and off(we use this frequently so I know how it works)
As for the advise give we seem to be on the right path though I am not sure what the actual difference is between execute as and impersonate. Is this not very much the same and adding it to the Stored procedure should be sufficient or am I wrong. Using impersonation on the login I am unsure of as it seems risky
The use of guest on the database - is that the issue, it seems to be a risk and we do not have it on any of our databases, and our one environment works even without the guest user.
Just as an extra note. The error Is the the server principal is not able to access the database (this database is DB1). On one of our environments we then setup another DB call it DB3, and it worked and the stored procedure could be executed linking to the same DB1(same superuser etc). This is not the solution as we have client environments where we cannot just recreate DB2.