sql-server - SQL Server 2019 - 服务器主体 "sa"在当前安全上下文下无法访问数据库 "DB_NAME"

Question

我编写了以下存储过程:

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

像这样运行:

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

我在 SQL Server 2019 STD 版 (RTM-CU6) (KB4563110) 中从 Master 运行，出现此错误:

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.

这个想法是使用存储过程将登录映射到数据库并赋予 db_owner 角色。
知道如何解决错误吗？

Answer 1

删除 WITH EXECUTE AS owner