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?

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

Answer 2

Remove the WITH EXECUTE AS owner