SQL Server 2019 - The server principal "sa" is not able to access the database "DB_NAME" under the current security context

Question

I've written the following stored procedure:

CREATE PROCEDURE dbo.usp_DEMO 
    @LOGINSQL VARCHAR(30), 
    @DBNAME VARCHAR(40) 
WITH EXECUTE AS owner
AS 
    DECLARE @SQL NVARCHAR(1000) 
    SET @SQL = 'USE' 
    SET @SQL = @SQL + ' ' + @DBNAME + ' ' + ' CREATE USER ' + ' ' 
               + @LOGINSQL + ' ' + ' FOR LOGIN ' + ' ' + @LOGINSQL + ' ' 
               + ' ALTER ROLE [DB_OWNER] ADD MEMBER ' + ' ' 
               + @LOGINSQL 
    EXEC sp_executesql @SQL

Running like this:

use master
go
exec usp_DEMO '<LOGIN>','<DATABASE>'

I'm running from Master in a SQL Server 2019 STD Edition (RTM-CU6) (KB4563110) and I get this error:

Msg 916, Level 14, State 2, Line 14
The server principal "<OWNER_LOGIN>" is not able to access the database "<DB_NAME>" under the current security context.

The idea is using a stored procedure to map a login to database and give db_owner role.

Any idea how can I solve the error?

Tickler

Answer 1

Check the target database owner and if the database owner is not sa or the TRUSTWORTHY property of the target database is OFF, you will receive the "The server principal "sa" is not able to access the database "DB_NAME" under the current security context" error when impersonating SA by using the EXECUTE AS in a stored procedure:

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188304(v=sql.105)?redirectedfrom=MSDN:

When impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

Options resolve the error "The server principal "sa" is not able to access the database "DB_NAME" under the current security context":

Note: In the below options replace {{TargetDb}} with actual DB_NAME

1. Change the target database owner to sa

USE {{TargetDb}} 
sp_changedbowner 'sa'

2. Or turn on the TRUSTWORTHY property in the target database:

ALTER DATABASE {{TargetDb}} SET TRUSTWORTHY ON

Gaitskell

Thanks! Actually I've tried both solutions already and none worked! :( Maybe some kind of restriction in 2019? – Tickler

Answer 2

Remove the WITH EXECUTE AS owner

Gaylene

HI Marcus, thanks for replying, but, actually the idea is using execute as for being able to run the command – Tickler

Answer 3

I've just solved quite similar situation as you had. In my scenario I wanted a user with minimum security privileges to be able to restore a DB and add some SQL users with roles to it. Stored procedure with EXECUTE AS worked for restoring the DB but was not able to make it work for creation of those users. Probably because of the Dynamic SQL + cross database nature of the issue.

In the end I was able to solve it with the help of the stored procedure signing

CREATE CERTIFICATE ElevationCertificate
ENCRYPTION BY PASSWORD = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'  
WITH SUBJECT = 'For privileges elevation',   
EXPIRY_DATE = '1/10/2025';  
GO

ADD SIGNATURE TO MyStoredProcedure   
BY CERTIFICATE ElevationCertificate  
WITH PASSWORD = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx';  
GO


CREATE LOGIN ElevatedUser
    FROM CERTIFICATE ElevationCertificate;
GO
   
EXEC master..sp_addsrvrolemember @loginame = N'ElevatedUser', @rolename = N'sysadmin'

Generable

Answer 4

I found another answer for 'Msg 916, Level 14, State 2, Line 63'

My DBs are not using TrustWorthy and the owner is our 'sa' account.

But the AD group I used for access is a Global Distribution group now Security.

My user got the same error and once I made a new AD Security Group the team got in.

to quote Reaces:

No this won't work.

For permissions in AD to properly propagate, the members of a security group have to be security principals. Meaning that each object requires an active SID that can then be used to chain the permissions from one member to the next. Because a distribution group has an SID that is not active, it breaks the chain.

Weider