How to Clone a SQL Server Login, Part 2 of 3

Problem

I need to create a login for my SQL Server and assign it the correct permissions. I already have another login which has the exact permissions I want. Is there a way I can clone the login that has all the permissions?

Solution

As mentioned in the first tip of this series, Microsoft SQL Server does not provide a built-in way to clone a login. However, SQL Server does provide all of the information we need to accomplish the cloning on our own. Since the first tip looked at server role memberships and permissions, this tip will focus on database role memberships and permissions.

When we consider a login at the database level, here are the considerations we have to undertake:

Whether or not the login has access to the database.
Creating the database user if the login to clone does have access to the database.
Database role memberships
Permissions on the database itself
Permissions on any schemas in the database
Permissions on individual objects

That last one is simple to say, but in the database there are a lot of different types of objects to consider. Therefore, the query we build to implement a copying of the permissions will be fairly lengthy. More on that in a bit.

A Quick Setup

This setup assumes you do have Bobby and Jimmy created and assigned the appropriate server level permissions and role memberships, as per the first tip. While the server permissions aren't necessary to demonstrate how to clone the permissions at the database level, the existence of the logins for Bobby and Jimmy are. Also, when we put everything together, including the work from the first tip, it'll be important that Bobby is set up properly. Also, the DBAWork database and the CloneLogin stored procedure are assumed to exist.

Here are the database specific setup commands:

USE master;
GO 

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'TestA')
  DROP DATABASE TestA;
  
CREATE DATABASE TestA;
GO 

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'TestB')
  DROP DATABASE TestB;
  
CREATE DATABASE TestB;
GO 

USE TestA;
GO 

CREATE USER [Bobby] FROM LOGIN [Bobby];
GO 

EXEC sp_addrolemember @rolename = 'db_securityadmin', @membername = 'Bobby';
GO 

CREATE ROLE TestRoleInTestA;
GO 

EXEC sp_addrolemember @rolename = 'TestRoleInTestA', @membername = 'Bobby';
GO 

GRANT SELECT ON SCHEMA::dbo TO [Bobby];
GO 

GRANT CREATE TABLE TO [Bobby];
GO 

GRANT CREATE PROCEDURE TO [Bobby] WITH GRANT OPTION;
GO 

USE TestB;
GO 

CREATE USER [Bobby] FROM LOGIN [Bobby];
GO 

GRANT IMPERSONATE ON USER::dbo TO [Bobby];
GO 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0m3Str0ng!!P4ssw0rd@';
GO 

CREATE ASYMMETRIC KEY ASymKey WITH ALGORITHM = RSA_2048;
GO 

GRANT CONTROL ON ASYMMETRIC KEY::ASymKey TO [Bobby];
GO 

CREATE CERTIFICATE TestCert 
WITH SUBJECT = 'A Test Cert to Show Permission Cloning';
GO 

GRANT VIEW DEFINITION ON CERTIFICATE::TestCert TO [Bobby];
GO 

CREATE SYMMETRIC KEY SymKey1 WITH ALGORITHM = AES_256 
ENCRYPTION BY ASYMMETRIC KEY ASymKey;
GO 

GRANT CONTROL ON SYMMETRIC KEY::SymKey1 TO [Bobby];
GO 

CREATE SYMMETRIC KEY SymKey2 WITH ALGORITHM = AES_256 
ENCRYPTION BY CERTIFICATE TestCert;
GO 

GRANT CONTROL ON SYMMETRIC KEY::SymKey2 TO [Bobby];
GO 

CREATE PROCEDURE dbo.SimpleProc
AS 
BEGIN
  SET NOCOUNT ON;

  SELECT 'Test Procedure';
END;
GO 

GRANT EXECUTE ON dbo.SimpleProc TO [Bobby];
GO

There are a lot of permissions to grant because we have a lot of permissions to verify are cloned.

Checking to See If the Login to Clone Has Database Access

The first thing we'll need to do is check to see if the login we're cloning has database access. If it doesn't, there's no more work to do. After all, not every login has access to every database.

SET NOCOUNT ON;

DECLARE @NewLogin sysname;
DECLARE @LoginToClone sysname;
DECLARE @TSQL nvarchar(max);
DECLARE @Return int;

SET @LoginToClone = 'Bobby';
SET @NewLogin = 'Jimmy';

IF EXISTS(SELECT name FROM sys.database_principals WHERE name = @LoginToClone)
BEGIN
  SET @TSQL = 'CREATE USER [' + @NewLogin + '] FROM LOGIN [' + @NewLogin + '];';
  EXEC @Return = sp_executesql @TSQL;
END;

You'll want to run this in both databases. As of right now, the script doesn't tell you much. It'll run and you'll get a successful result. We'll add more to it when we turn it into a stored procedure.

However, if you check the users folder under the database, you'll see that Jimmy was created:

Database Role Memberships

Of all the things we need to do to clone database permissions, handling database role memberships is the easiest part.

In our database setup, we only have database role memberships defined in the TestA database. So you'll only need to run the script there. In the first tip, we merely wrote out the appropriate commands to handle the role memberships. That is, until we built the final stored procedure. Given that the joins are similar, we're going to go ahead and build the execution as well:

SET NOCOUNT ON;

DECLARE @NewLogin sysname;
DECLARE @LoginToClone sysname;
DECLARE @TSQL nvarchar(max);
DECLARE @Return int;

SET @LoginToClone = 'Bobby';
SET @NewLogin = 'Jimmy';

DECLARE cursDBRoleMembersSQL CURSOR FAST_FORWARD
FOR
SELECT 'EXEC sp_addrolemember @rolename = ''' + r.name 
  + ''', @membername = ''' + @NewLogin + ''';'
FROM sys.database_principals AS U
  JOIN sys.database_role_members AS RM
    ON U.principal_id = RM.member_principal_id
  JOIN sys.database_principals AS R
    ON RM.role_principal_id = R.principal_id
WHERE U.name = @LoginToClone;

OPEN cursDBRoleMembersSQL;

FETCH FROM cursDBRoleMembersSQL INTO @TSQL;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  PRINT @TSQL;
  EXECUTE @Return = sp_executesql @TSQL;

  IF (@RETURN <> 0)
  BEGIN
    ROLLBACK TRAN;
	RAISERROR('Error encountered assigning DB role memberships.', 16, 1);
	-- When we roll this into the stored procedure, we'll also add
	-- CLOSE and deallocate commands for the cursor as well as a return.
  END;

  FETCH NEXT FROM cursDBRoleMembersSQL INTO @TSQL;
END;

CLOSE cursDBRoleMembersSQL;
DEALLOCATE cursDBRoleMembersSQL;

And when you execute the script and check on Jimmy, you'll see that two T-SQL commands should have been specified and executed:

Execution of T-SQL to add role memberships

Checking visually, we can see Jimmy is a member of the two roles which Bobby is a member of as well:

Database Permissions

Like with server permissions, there are a lot of objects we need to handle in order to clone all the possible permissions. They are:

Permissions at the database level
Permissions on schema
Permissions on objects contained within a schema
Permissions on users
Permission on the three types of encryption objects: asymmetric keys, certificates, and symmetric keys

This does mean I'm leaving out some things: assemblies, service broker pieces, data types, and full text catalogs among them. You can easily extend the script to include those things using UNION ALL if you are using them in your environment. As it is, the SQL code is rather long:

SET NOCOUNT ON;

DECLARE @NewLogin sysname;
DECLARE @LoginToClone sysname;
DECLARE @TSQL nvarchar(max);
DECLARE @Return int;

SET @LoginToClone = 'Bobby';
SET @NewLogin = 'Jimmy';

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON DATABASE::[' 
     + DB_NAME() + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON DATABASE::[' 
     + DB_NAME() + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
WHERE class = 0
  AND P.[type] <> 'CO'
  AND U.name = @LoginToClone
  
UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON SCHEMA::[' 
     + S.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON SCHEMA::[' 
     + S.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.schemas AS S
    ON S.schema_id = P.major_id
WHERE class = 3
  AND U.name = @LoginToClone
  
UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON OBJECT::[' 
     + O.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON OBJECT::[' 
     + O.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.objects AS O
    ON O.object_id = P.major_id
WHERE class = 1
  AND U.name = @LoginToClone
  AND P.major_id > 0
  AND P.minor_id = 0

UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON OBJECT::[' 
     + O.name + '] (' + C.name + ') TO [' + @NewLogin + '] WITH GRANT OPTION;' 
	 COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON OBJECT::[' 
     + O.name + '] (' + C.name + ') TO [' + @NewLogin + '];' 
	 COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.objects AS O
    ON O.object_id = P.major_id
  JOIN sys.columns AS C
    ON C.column_id = P.minor_id AND o.object_id = C.object_id
WHERE class = 1
  AND U.name = @LoginToClone
  AND P.major_id > 0
  AND P.minor_id > 0

UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON USER::[' 
     + U2.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON USER::[' 
     + U2.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.database_principals AS U2
    ON U2.principal_id = P.major_id
WHERE class = 4
  AND U.name = @LoginToClone

UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON SYMMETRIC KEY::[' 
     + K.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON SYMMETRIC KEY::[' 
     + K.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.symmetric_keys AS K
    ON P.major_id = K.symmetric_key_id
WHERE class = 24
  AND U.name = @LoginToClone

UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON ASYMMETRIC KEY::[' 
     + K.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON ASYMMETRIC KEY::[' 
     + K.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.asymmetric_keys AS K
    ON P.major_id = K.asymmetric_key_id
WHERE class = 26
  AND U.name = @LoginToClone

UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON CERTIFICATE::[' 
     + C.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON CERTIFICATE::[' 
     + C.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.certificates AS C
    ON P.major_id = C.certificate_id
WHERE class = 25
  AND U.name = @LoginToClone;

If we run this against the TestA database, we'll get the following results back:

And here are the results for the TestB database:

Building a Stored Procedure

While we could have the script saved off and pull it up each time we needed to clone database permissions, what would be better is if we already have it in a stored procedure. In actuality, doing this as a stored procedure means we can simplify the SELECT statement and not have to rely on all the UNION ALLs to build the full list of permissions.

Instead, what we'll do is gather the permissions for each type of object and then make the permissions change. This does mean the use of several cursors, but this is a row-by-row operations. Here's the script to build the stored procedure:

USE DBAWork;
GO 

CREATE PROC dbo.CloneDBPerms
  @NewLogin sysname,
  @LoginToClone sysname,
  @DBName sysname
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL nvarchar(max);
	DECLARE @Return int;

	CREATE TABLE #DBPermissionsTSQL 
	(
		PermsTSQL nvarchar(MAX)
	);


	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON DATABASE::[' 
		 + @DBName + '] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON DATABASE::[' 
		 + @DBNAME + '] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	WHERE class = 0
	  AND P.[type] <> ''CO''
	  AND U.name = ''' + @LoginToClone + ''';';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON SCHEMA::['' 
		 + S.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON SCHEMA::['' 
		 + S.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.schemas AS S
		ON S.schema_id = P.major_id
	WHERE class = 3
	  AND U.name = ''' + @LoginToClone + ''';';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON OBJECT::['' 
		 + O.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON OBJECT::['' 
		 + O.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.objects AS O
		ON O.object_id = P.major_id
	WHERE class = 1
	  AND U.name = ''' + @LoginToClone + '''
	  AND P.major_id > 0
	  AND P.minor_id = 0';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON OBJECT::['' 
		 + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '] WITH GRANT OPTION;'' 
		 COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON OBJECT::['' 
		 + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '];'' 
		 COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.objects AS O
		ON O.object_id = P.major_id
	  JOIN [' + @DBName + '].sys.columns AS C
		ON C.column_id = P.minor_id AND o.object_id = C.object_id
	WHERE class = 1
	  AND U.name = ''' + @LoginToClone + '''
	  AND P.major_id > 0
	  AND P.minor_id > 0;'
	
	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON USER::['' 
		 + U2.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON USER::['' 
		 + U2.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.database_principals AS U2
		ON U2.principal_id = P.major_id
	WHERE class = 4
	  AND U.name = ''' + @LoginToClone + ''';';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON SYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON SYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.symmetric_keys AS K
		ON P.major_id = K.symmetric_key_id
	WHERE class = 24
	  AND U.name = ''' + @LoginToClone + ''';';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON ASYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON ASYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.asymmetric_keys AS K
		ON P.major_id = K.asymmetric_key_id
	WHERE class = 26
	  AND U.name = ''' + @LoginToClone + ''';';
	
	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON CERTIFICATE::['' 
		 + C.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON CERTIFICATE::['' 
		 + C.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.certificates AS C
		ON P.major_id = C.certificate_id
	WHERE class = 25
	  AND U.name = ''' + @LoginToClone + ''';';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	DECLARE cursDBPermsSQL CURSOR FAST_FORWARD
	FOR
	SELECT PermsTSQL FROM #DBPermissionsTSQL

	OPEN cursDBPermsSQL;

	FETCH FROM cursDBPermsSQL INTO @SQL;

	WHILE (@@FETCH_STATUS = 0)
	BEGIN
	  SET @SQL = 'USE [' + @DBName + ']; ' + @SQL;

	  PRINT @SQL;
	  EXEC @Return = sp_executesql @SQL;

	  IF (@Return <> 0)
	  BEGIN
		  ROLLBACK TRAN;
		  RAISERROR('Error granting permission', 16, 1);
		  CLOSE cursDBPermsSQL;
		  DEALLOCATE cursDBPermsSQL;
		  RETURN(1);
	  END;

	  FETCH NEXT FROM cursDBPermsSQL INTO @SQL;
	END;

	CLOSE cursDBPermsSQL;
	DEALLOCATE cursDBPermsSQL;
	DROP TABLE #DBPermissionsTSQL;
END;
GO

Next Steps

Review the first tip in the series on cloning logins.
Learn why you should default to Windows groups when possible as a best practice.
Find out what tables and views to use to audit logins.
Stay tuned for my next tip on automating this process.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips