0

I have 2 logins :

  • LoginA: role dbcreator + CONNECT ANY DATABASE
  • LoginB: role sysadmin

I have 2 databases :

  • dbA: everyone has all rights on it
  • dbB: only LoginB has rights on it, LoginA is only the owner of that database but no more

I'm wondering if it is possible to create a user (linked to LoginA) in dbB from a stored procedure of dbA.
Considering this stored procedure has to be executed by LoginA.
Maybe some kind of "EXECUTE AS" mechanism could help ?
Also I have issues about how to use a "USE [dbB]" in a stored procedure.

The solution can have some rights modification,
but these can't give securityadmin nor sysadmin roles.

Here is what is done for now but clearly there is a lot of debug and functionalities to go, but the spirit of what I wanna do is in here :

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
1

Since LoginA is not member of sysadmin server role, the login must need IMPERSONATE permissions to use EXECUTE AS i.e.

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"

Once LoginA got IMPERSONATE access, you can create procedure as follows in dbA where LoginA got full access (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

Call procedure as follows:

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


If the target is multiple databases, then LoginA must be either member of sysadmin server role or must be user of all databases (under public role) and GRANTED for IMPERSONATE on respective database's db_owner

  • 1
    This answer helped me a lot, but I modified it a bit on my current context. Used a EXECUTE as login instead of user on database [master] and GRANT IMPERSONATE ON LOGIN::LoginB to LoginA. By the way instead of creating the user or giving sysadmin role, I only gave CONNECT ANY DATABASE right to LoginA. – Hybris95 Oct 22 at 8:15
  • 1
    @Hybris95, That's great, as long as LoginB is sysadmin or db_owner of all databases – Shekar Kola Oct 22 at 8:44
  • LoginB is sa ;) – Hybris95 Oct 22 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.