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

Question

我开发了一个存储过程，用于从备份文件还原数据库并添加应用程序用户。此存储过程属于主数据库。

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

我可以还原数据库，但无法找到在此过程结束时添加用户的方法。

我使用的代码：

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语句从存储过程中在master db中创建一个动态参数db名称的用户吗？

谢谢

- Bruno Deprez

---

使用 EXECUTE AS 'dbo' - bummi

问题与模拟身份（即EXECUTE AS）及其限制有关。我正在努力回答。顺便问一下，他们正在审查这个过程吗？你不能通过SQL注入授予自己“sysadmin”服务器角色吗？;-) - Solomon Rutzky

实际上不是这样的。我刚试图创建登录，但它没有起作用。我几乎可以确定这是与脚本未按预期工作的原因相同。等一下。 - Solomon Rutzky

我的IT部门不想授予任何人sysadmin权限，只允许我们使用EXECUTE AS。感谢您的帮助。 - Bruno Deprez

Answer 1

您面临的问题是模拟用户（即通过EXECUTE AS切换安全上下文）的限制。对于数据库范围对象，例如存储过程，您在EXECUTE AS子句中指定了一个数据库级别的user，而不是服务器级别的login。因此，该存储过程实际上并没有充当sysadmin。但是，有一种方法可以安全地授予仅此一个存储过程真正的sysadmin权限，以便它可以执行您动态SQL中的步骤，即：

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

要做到这一点，可以使用证书签名存储过程。然后，该证书还将用于创建一个服务器级别的登录，该登录将添加到sysadmin服务器角色中。然后，当任何具有对此存储过程的EXECUTE权限的用户/登录执行此过程时，它将仅通过由同一证书签名而获得证书基于登录的权限。

第1步：在[master]数据库中设置证书：

USE [master];
GO

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

步骤二：创建登录并将其添加到sysadmin角色中：

CREATE LOGIN [BackupRestoreOps] FROM CERTIFICATE [BackupRestoreCert];

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

第三步：使用该证书签署存储过程，创建到新登录的链接：

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

所有上述操作都是在[master]数据库的上下文中完成的，尽管为了说明更清晰一些，它被拆分成了几个步骤。但实际上，这将是IT人员运行一次的单个脚本。当然，如果您ALTER存储过程，他们将不得不再次运行ADD SIGNATURE命令（即第3步），因为它会在任何更改存储过程定义时丢失。

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

第4步（也许）：您可能可以从存储过程中删除EXECUTE AS子句。如果它传递了任何允许最初还原操作工作的权限，则应该现在通过基于证书的登录来假定它们，因为它被标记为sysadmin。

免责声明 我想/需要明确的是，考虑到问题中指定的存储过程，因为它是写死的，一旦授予权限，就会允许SQL注入。当然，这已经是IT人员决定使用EXECUTE AS子句授予sysadmin权限给此存储过程的原意所导致的后果。我现在只是指出上述步骤实际上会使他们预期的行为成为现实。如果在动态SQL之上有任何步骤，如果传递了除数据库名称以外的任何内容，它们将出错，那么很好，但这仍然需要针对那些可能试图复制并粘贴而不理解完整上下文的其他人进行说明。

应该做两件事情：

1. While a minor point, the datatype of the input parameter should be sysname (alias for NVARCHAR(128)) as that is how [name] is defined in [sys].[databases].

2. The main thing to do is verify that the value passed in is an existing database name, something along the lines of:

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

我尝试寻找另外一种服务器角色，以允许不使用如此特权的角色实现这一点。我已经尝试了dbcreator、securityadmin、serveradmin和setupadmin，但不幸的是，它们都没有起作用 :(。

- Solomon Rutzky