sql-server - SQL 服务器 : EXECUTE AS clause of stored procedure not granting sysadmin permissions

Question

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

问题是我的 IT 部门不允许我使用 admin 用户，只能对 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

当我运行它时，我收到以下错误消息:

The server principal "aSysAdminUser" is not able to access the database "database" under the current security context.

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

谢谢

Answer 1

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

连接到数据库

创建用户

改变角色

执行此操作的方法是使用证书对存储过程进行签名。然后，该证书还将用于创建服务器级登录，该登录将添加到 sysadmin服务器角色。然后，当任何用户/登录名(对该存储过程具有 EXECUTE 权限)执行此过程时，它将仅通过由同一证书签名来获取基于证书的登录名的权限。

第一步:在 [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 注入(inject)。当然，这已经是 IT 人员决定使用 EXECUTE AS 的初衷的结果。授予条款 sysadmin此存储过程的权限。我现在只是指出，上述步骤实际上将使他们的预期行为成为现实。如果动态 SQL 上方的步骤在传入数据库名称以外的任何内容时会出错，那很好，但是对于可能希望复制此内容并只是复制/粘贴而不了解完整内容的其他人，仍然需要说明这一点语境。

有两件事应该做:

虽然是小问题，但输入参数的数据类型应该是 sysname (NVARCHAR(128) 的别名)因为这就是 [name]在 [sys].[databases] 中定义.

要做的主要事情是验证传入的值是一个现有的数据库名称，类似于:

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

编辑:
我已尝试找到另一个允许此操作的服务器角色，它不是 sysadmin。希望不要使用这样的特权角色。我试过 dbcreator , securityadmin , serveradmin , 和 setupadmin ，但不幸的是他们都没有工作:(。