Início / dba / Perguntas / 251374
Accepted
Hybris95
Asked: 2019-10-18 06:31:49 +0800 CST

Crie um usuário no banco de dados A com um procedimento do banco de dados B (com EXECUTE AS)

  • 772

Tenho 2 logins:

  • LoginA: role dbcreator + CONECTE QUALQUER BANCO DE DADOS
  • LoginB: função sysadmin

Tenho 2 bancos de dados:

  • dbA: todos têm todos os direitos sobre ele
  • dbB: apenas LoginB tem direitos sobre ele, LoginA é apenas o proprietário desse banco de dados, mas não mais

Gostaria de saber se é possível criar um usuário (vinculado ao LoginA) em dbB a partir de um procedimento armazenado de dbA.
Considerando que este procedimento armazenado deve ser executado por LoginA.
Talvez algum tipo de mecanismo "EXECUTE AS" possa ajudar?
Também tenho problemas sobre como usar um "USE [dbB]" em um procedimento armazenado.

A solução pode ter algumas modificações de direitos,
mas elas não podem dar funções securityadmin nem sysadmin.

Aqui está o que está feito por enquanto, mas claramente há muita depuração e funcionalidades a serem feitas, mas o espírito do que eu quero fazer está aqui:

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 respostas

  1. Best Answer

    Como LoginAnão é membro da sysadminfunção de servidor, o logon deve precisar IMPERSONATEde permissões para usar EXECUTE AS, ou seja,

    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"

    Uma vez LoginAobtido o acesso IMPERSONATE, você pode criar o procedimento da seguinte forma em dbAonde LoginAobteve acesso total (db_owner)

    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

    Procedimento de chamada da seguinte forma:

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

    Se o destino for vários bancos de dados, LoginAdeve ser membro da sysadminfunção de servidor ou deve ser usuário de todos os bancos de dados (sob função pública) e CONCEDIDO para IMPERSONATE nos respectivos bancos de dadosdb_owner

    • 1

relate perguntas