This error wasted my morning. Although I have solved this issue before, it took a while to google and fix it again. So, this post is for my own edification and reference if for no other reason!
–Proc [MyProcedureName] started AT:2020-10-13 09:49:43.4632735
Msg 916, Level 14, State 1, Procedure MySchemaName.MyProcedureName, Line 31 [Batch Start Line 6]
The server principal “elevated_user_login” is not able to access the database “my_database” under the current security context.
Before you go on to use the solution, please be warned that there are numerous issues with doing this and I highly recommend reading this post:
- [domain\elevated_user_login] user exists as an user in both databases A and B
- Login [domain\elevated_user_login] has elevated privileges in databases A and B
- When the two queries below are run in both databases A and B, the SID is the same (confirming that the user is not orphaned in A or B)
SELECT sid FROM sys.sysusers WHERE name = 'mydomain\elevated_user_login' SELECT sid FROM sys.syslogins WHERE name = 'mydomain\elevated_user_login';
- Users with minimal permissions run stored procedures in Database A that have “EXECUTE AS ‘domain\elevated_user_login'” (example below)
- When a regular user with minimal permissions executes the stored procedure in Database A, the error in the title pops up when referencing a table in Database A.
USE DatabaseA GO ALTER PROCEDURE dbo.Truncate_Table_Example WITH EXECUTE AS 'mydomain\elevated_user_login' AS DECLARE @count int = 1; BEGIN --Table in Database A (this will be fine) TRUNCATE TABLE [DatabaseA].[dbo].[Example]; --Reference to a table in Database B (Error happens on this line) SELECT @count = COUNT(1) FROM B.dbo.TableInDatabaseB; END; GO
ALTER DATABASE [DatabaseA] SET TRUSTWORTHY ON ALTER DATABASE [DatabaseB] SET TRUSTWORTHY ON