SQL Server: Fix – The server principal “elevated_user_login” is not able to access the database “my_database” under the current security context

The Error:

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.

Warning:

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:

The Scenario:

  • [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

The Solution:

The solution it turns out is pretty simple as long as the obvious things are in place. This thread was very helpful and in the end SET TRUSTWORTHY has to be ON.

ALTER DATABASE [DatabaseA] SET TRUSTWORTHY ON
ALTER DATABASE [DatabaseB] SET TRUSTWORTHY ON

3 thoughts on “SQL Server: Fix – The server principal “elevated_user_login” is not able to access the database “my_database” under the current security context

    1. Thank you so much for the link! It has a ton of useful information on this subject that will make for some good reading to make an informed decision based on the specific situation. In my case TRUSTWORTHY is needed for EXECUTE AS access since the SP code uses dynamic SQL. As the author says “Good for the lazy and casual. :-)”!

      1. Yes, one must set aside quite a few hours to read the article.
        But then it is important to continue reading all his other articles.
        They are frequently updated too, so one must keep coming back.
        But totally worth your time…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s