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?

score 5 · Answer 1 · answered Sep 02 '20 at 03:27

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

Change the target database owner to sa

USE {{TargetDb}} 
sp_changedbowner 'sa'

Or turn on the TRUSTWORTHY property in the target database:

ALTER DATABASE {{TargetDb}} SET TRUSTWORTHY ON

Thanks! Actually I've tried both solutions already and none worked! :( Maybe some kind of restriction in 2019? — AdemirP, Sep 02 '20 at 11:31

score 1 · Answer 2 · answered Sep 02 '20 at 03:13

1

Remove the WITH EXECUTE AS owner

answered Sep 02 '20 at 03:13

Marcus Vinicius Pompeu

1,219
1
11
24

HI Marcus, thanks for replying, but, actually the idea is using execute as for being able to run the command – AdemirP Sep 02 '20 at 11:28

score 1 · Answer 3 · answered Jul 02 '23 at 22:14

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'

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

3 Answers3