SQL Server：存储过程的 EXECUTE AS 子句未授予 sysadmin 权限

我开发了一个存储过程，以便从备份文件恢复数据库并向其中添加应用程序用户。该存储过程属于master 数据库。

问题是我的 IT 部门不允许我使用管理员用户，只能使用 sysadmin 用户的 EXECUTE AS 语句。

我可以恢复数据库，但找不到在过程结束时添加用户的方法。

我使用的代码：

CREATE PROCEDURE [dbo].[myProc] 
@database VARCHAR(50)
WITH EXECUTE AS 'aSysAdminUser' 
AS
BEGIN
    --Restore the database (working)
    --Add the application user    (not working)                  
    SET @sqlScript = 'USE '+@database +';
    CREATE USER [myApplicationUser] FROM LOGIN [myApplicationUser];
    EXEC sp_addrolemember ''db_owner'', ''myApplicationUser'''
    EXEC(@sqlScript)
END

当我运行它时，出现以下错误消息：

服务器主体“aSysAdminUser”无法 访问当前安全上下文下的数据库“database”。

知道如何使用 EXECUTE AS 语句从主数据库上的存储过程在动态参数数据库名称中创建用户吗？

Thanks

您面临的问题是模拟的限制（即通过切换安全上下文EXECUTE AS）。对于数据库范围的对象，例如您指定数据库级别的存储过程user in the EXECUTE AS子句，不是服务器级别的login。因此，这个程序并不能真正起到sysadmin。但是，有一种方法可以安全地授予只是这一个存储过程 true sysadmin允许它执行动态 SQL 中的步骤的权限，即：

1. 连接到数据库
2. 创建用户
3. 改变角色

执行此操作的方法是使用证书对存储过程进行签名。然后，该证书还将用于创建服务器级登录名，该登录名将添加到sysadmin服务器角色。然后，当任何用户/登录（具有EXECUTE对此存储过程的权限）执行此过程，只需由同一证书签名即可获得基于证书的登录的权限。

Step 1:在中设置证书[master]数据库：

USE [master];
GO

CREATE CERTIFICATE [BackupRestoreCert]
    ENCRYPTION BY PASSWORD = N'MyPassword'
    WITH SUBJECT = N'Certificate for Managing Backup/Restore Operation Permissions';
GO

Step 2:创建登录名并添加到sysadmin role:

CREATE LOGIN [BackupRestoreOps] FROM CERTIFICATE [BackupRestoreCert];

ALTER SERVER ROLE [sysadmin] ADD MEMBER [BackupRestoreOps];

Step 3:使用该证书签署存储过程，创建指向新登录名的链接：

ADD SIGNATURE
    TO [dbo].[myProc]
    BY CERTIFICATE [BackupRestoreCert]
    WITH PASSWORD = 'MyPassword';

以上所有内容实际上都是在[master]数据库，尽管将其分解的解释似乎更具可读性。但是，实际上，IT 人员只运行一次单个脚本。当然，如果你曾经ALTER该存储过程，他们将必须运行ADD SIGNATURE再次命令（即步骤 3），因为它会在过程定义发生任何更改时丢失。

就是这样。我已经使用问题中显示的存储过程对此进行了测试，一旦我添加了签名和sysadmin角色，创建用户并将其添加到db_owner role.

第 4 步（也许）：你也许可以删除EXECUTE AS存储过程中的子句。如果它确实传递了允许初始恢复操作工作的任何权限，那么现在应该通过基于证书的登录来假定这些权限，因为它被标记为sysadmin.

免责声明
我只想/需要明确指出，据了解，鉴于问题中指定的存储过程，如其所写，will一旦授予此权限，就允许 SQL 注入。当然，这已经是IT人员决定使用该技术的初衷的结果。EXECUTE AS授予条款sysadmin对此存储过程的权限。我现在只是指出，上述步骤实际上将使他们的预期行为成为现实。如果动态 SQL 之上的步骤在传入数据库名称以外的任何内容时会出错，那就太好了，但这仍然需要为其他可能想要复制此内容并且只是复制/粘贴而不理解完整内容的人说明。语境。

有两件事应该做：

1. 虽然是次要的一点，但输入参数的数据类型应该是sysname（别名为NVARCHAR(128)）就像这样[name]定义于[sys].[databases].

2. 要做的主要事情是验证传入的值是否是现有的数据库名称，大致如下：

   IF (DB_NAME(@database) IS NULL)
   BEGIN
      RAISERROR(N'Invalid Database Name', 16, 1);
      RETURN;
   END;
   

EDIT:
我尝试寻找另一个服务器角色来实现这一点，但不是sysadmin希望不使用这样的特权角色。我努力了dbcreator, securityadmin, serveradmin, and setupadmin，但不幸的是它们都不起作用:(。