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 dbAdbA
where LoginA
got full access (db_owner)
use dbA
go
CREATE PROCEDURE [UserAfterRestore]
@TargetDBName varchar(128),
@GranteeLogin varchar(128),
@ExectuteAs@ExecuteAs varchar(128)
AS
BEGIN
SET NOCOUNT ON;
Declare @TSQL varchar(2000);
SELECT @TSQL = 'USE [' + @DBName + '];
Execute as user = '''+ @ExectuteAs@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',
@ExectuteAs@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
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),
@ExectuteAs varchar(128)
AS
BEGIN
SET NOCOUNT ON;
Declare @TSQL varchar(2000);
SELECT @TSQL = 'USE [' + @DBName + '];
Execute as user = '''+ @ExectuteAs +''';
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',
@ExectuteAs = 'LoginB';
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