Create a user on database A with a procedure from database B (with EXECUTE AS)

Question

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

Answer 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