主页 / dba / 问题 / 251374
Accepted
Hybris95
Asked: 2019-10-18 06:31:49 +0800 CST

使用数据库 B 中的过程在数据库 A 上创建用户（使用 EXECUTE AS）

  • 772

我有 2 个登录名：

  • LoginA：角色 dbcreator + 连接任何数据库
  • LoginB：角色系统管理员

我有 2 个数据库：

  • dbA：每个人都拥有它的所有权利
  • dbB：只有 LoginB 对其拥有权限，LoginA 只是该数据库的所有者，但没有更多

我想知道是否可以从 dbA 的存储过程在 dbB 中创建一个用户（链接到 LoginA）。
考虑到这个存储过程必须由 LoginA 执行。
也许某种“EXECUTE AS”机制可以提供帮助？
我也有关于如何在存储过程中使用“USE [dbB]”的问题。

该解决方案可以进行一些权限修改，
但这些不能赋予 securityadmin 或 sysadmin 角色。

这是目前所做的，但显然还有很多调试和功能要做，但我想做的精神在这里：

CREATE PROCEDURE [dbB_UserAfterRestore] 
AS
BEGIN
    SET NOCOUNT ON;

    USE [dbB]
    CREATE USER [HOSTNAME\LoginA] FOR LOGIN [HOSTNAME\LoginA]
    ALTER ROLE [db_datareader] ADD MEMBER [HOSTNAME\LoginA]
    ALTER ROLE [db_datawriter] ADD MEMBER [HOSTNAME\LoginA]
END
GO
sql-server stored-procedures

1 个回答

  1. Best Answer

    由于LoginA不是sysadmin服务器角色的成员，登录必须需要IMPERSONATE权限才能使用EXECUTE ASie

    USE dbB;
CREATE USER LoginA for login LoginA; -- This will create user under "public" database role
GRANT IMPERSONATE ON USER::LoginB to LoginA; -- This is required to use "EXECUTE AS"

    获得IMPERSONATE访问权限后，您可以在获得完全访问权限 (db_owner)的位置LoginA创建如下过程dbALoginA

    use dbA
go

CREATE PROCEDURE [UserAfterRestore] 
    @TargetDBName varchar(128),
    @GranteeLogin varchar(128),
    @ExecuteAs varchar(128)
AS
BEGIN
    SET NOCOUNT ON;
    Declare @TSQL varchar(2000);

    SELECT @TSQL = 'USE [' + @DBName + ']; 
                    Execute as user = '''+ @ExecuteAs +'''; 
                    IF NOT EXISTS (SELECT name FROM SYS.database_principals WHERE name = '''+ @GranteeLogin +''')
                        BEGIN
                            CREATE USER [' + @GranteeLogin + '] FOR LOGIN ['+ @GranteeLogin + '];
                        END
                        ALTER ROLE [db_datareader] ADD MEMBER ['+ @GranteeLogin +'];
                        ALTER ROLE [db_datawriter] ADD MEMBER ['+ @GranteeLogin +'];'
    Print 'Executing: ' + @TSQL

    EXEC (@TSQL);
END
GO

    调用流程如下：

    exec dbA.dbo.[UserAfterRestore] 
        @TargetDBName = dbB, 
        @GranteeLogin = 'LoginA', 
        @ExecuteAs = 'LoginB';

    如果目标是多个数据库，则LoginA必须是sysadmin服务器角色的成员或必须是所有数据库的用户（在公共角色下），并且在各个数据库上授予 IMPERSONATEdb_owner

    • 1

相关问题