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

By:   |   Updated: 2015-06-04   |   Comments (4)   |   Related: More > Security

Problem

I have read part 1 and part 2 of your tip series and the tips are great. I have a large environment with numerous SQL Server databases where I need to clone logins.  How can I automate this code to save time and ensure accuracy?

Solution

If you have a lot of databases, running the queries (from the second tip) manually against each one and then copying the output into a query window and executing that query will take forever. Let's automate some of this.

First, let's wrap the permissions query into a stored procedure where the generated T-SQL code gets executed for us. We will break up the long SELECT statement into the individual parts and insert the commands into a temporary table. Note that I'm building this stored procedure in a database called DBAWork. Because of that, if I want the permissions in another database, I have to use the three part naming convention (database.schema.object) in order to get to the correct catalog views. Finally, when granting permissions, I have to ensure I'm in the database where the permissions are needed. That's why we'll prepend every T-SQL statement with a USE command.

If we look at Jimmy in TestA before we execute anything, we see he has no permissions:

Jimmy with no Permissions in TestA DB

When we execute the stored procedure, we'll see what permissions are being assigned:

Stored Procedure executing the permissions

And now when we check Jimmy, we'll see the new permissions. Well, you may not see the database level permissions I've highlighted below. In order to see it, click on the Search button, select the Specific Objects option, and click on Database and specify the TestA Database. It should then appear as a securable in the dialog window like you see below:

Jimmy with the cloned permission in TestA DB

Now let's also build stored procedures to create the user and to handle the role memberships.

First, creating the user:

USE DBAWork;
GO 

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

  DECLARE @TSQL nvarchar(MAX);
  DECLARE @Return int;

  BEGIN TRAN; 

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

  IF (@Return <> 0)
    BEGIN
	  ROLLBACK TRAN;
	  RAISERROR('Error creating user', 16, 1);
	  RETURN(1);
	END;

  COMMIT TRAN;
END;

Then, assigning the user to roles:

USE DBAWork;
GO 

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

  DECLARE @TSQL nvarchar(MAX);
  DECLARE @Return int;

  BEGIN TRAN; 

  CREATE TABLE #RoleMembershipSQL 
  (
    RoleMembersTSQL nvarchar(MAX)
  );

  SET @TSQL = 'INSERT INTO #RoleMembershipSQL (RoleMembersTSQL) 
	SELECT ''EXEC sp_addrolemember @rolename = '''''' + r.name 
      + '''''', @membername = ''''' + @NewLogin + ''''';''
    FROM [' + @DBName + '].sys.database_principals AS U
      JOIN [' + @DBName + '].sys.database_role_members AS RM
        ON U.principal_id = RM.member_principal_id
      JOIN [' + @DBName + '].sys.database_principals AS R
        ON RM.role_principal_id = R.principal_id
    WHERE U.name = ''' + @LoginToClone + ''';';

  EXEC @Return = sp_executesql @TSQL;

  IF (@Return <> 0)
  BEGIN
    ROLLBACK TRAN; 
	RAISERROR('Could not retrieve role memberships.', 16, 1);
	RETURN(1)
  END;

  DECLARE cursDBRoleMembersSQL CURSOR FAST_FORWARD
  FOR
  SELECT RoleMembersTSQL 
  FROM #RoleMembershipSQL;

  OPEN cursDBRoleMembersSQL;

  FETCH FROM cursDBRoleMembersSQL INTO @TSQL;

  WHILE (@@FETCH_STATUS = 0)
    BEGIN
	  SET @TSQL = 'USE [' + @DBName + ']; ' + @TSQL;
      EXECUTE @Return = sp_executesql @TSQL;

      IF (@RETURN <> 0)
        BEGIN
          ROLLBACK TRAN;
	      RAISERROR('Error encountered assigning DB role memberships.', 16, 1);
		  CLOSE cursDBRoleMembersSQL;
		  DEALLOCATE cursDBRoleMembersSQL;
        END;

      FETCH NEXT FROM cursDBRoleMembersSQL INTO @TSQL;
    END;

  CLOSE cursDBRoleMembersSQL;
  DEALLOCATE cursDBRoleMembersSQL;

  DROP TABLE #RoleMembershipSQL;

  COMMIT TRAN;
END;

The Master Script:

Now that we have all of our stored procedures, let's wrap everything up into one stored procedure to create the login, grant server level permissions, and then handle each database in turn:

USE DBAWork;
GO 

CREATE PROC dbo.CloneLoginAndAllDBPerms
  @NewLogin sysname,
  @NewLoginPwd NVARCHAR(MAX),
  @WindowsLogin CHAR(1),
  @LoginToClone sysname
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @Return int;

  BEGIN TRAN;

  EXEC @Return = DBAWork.dbo.CloneLogin 
    @NewLogin = @NewLogin, 
	@NewLoginPwd = @NewLoginPwd, 
	@WindowsLogin = @WindowsLogin, 
	@LoginToClone = @LoginToClone;

  IF (@Return <> 0)
	BEGIN
	  ROLLBACK TRAN;
	  RAISERROR('Exiting because login could not be created', 16, 1);
	  RETURN(1);
	END

  DECLARE @DBName sysname;
  DECLARE @SQL nvarchar(MAX);

  DECLARE cursDBs CURSOR FAST_FORWARD
  FOR
  SELECT name 
  FROM sys.databases 
  WHERE state_desc = 'ONLINE';

  OPEN cursDBs;

  FETCH FROM cursDBs INTO @DBName;

  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    EXEC @Return = DBAWork.dbo.CreateUserInDB 
	  @NewLogin = @NewLogin, 
	  @LoginToClone = @LoginToClone, 
	  @DBName = @DBName;

	IF (@Return <> 0)
	BEGIN
	  ROLLBACK TRAN;
	  RAISERROR('Exiting because user could not be created.', 16, 1);
	  RETURN(1);
	END;

	EXEC @Return = DBAWork.dbo.GrantUserRoleMembership
	  @NewLogin = @NewLogin, 
	  @LoginToClone = @LoginToClone, 
	  @DBName = @DBName;

	IF (@Return <> 0)
	BEGIN
	  ROLLBACK TRAN;
	  RAISERROR('Exiting because role meberships could not be granted.', 16, 1);
	  RETURN(1);
	END;

	EXEC @Return = DBAWork.dbo.CloneDBPerms
	  @NewLogin = @NewLogin, 
	  @LoginToClone = @LoginToClone, 
	  @DBName = @DBName;

	IF (@Return <> 0)
	BEGIN
	  ROLLBACK TRAN;
	  RAISERROR('Exiting because user could not be created.', 16, 1);
	  RETURN(1);
	END;

    FETCH NEXT FROM cursDBs INTO @DBName;
  END;

  CLOSE cursDBs;
  DEALLOCATE cursDBs;

  COMMIT TRAN;
END;

GO

In order to test this, we'll need to get rid of Jimmy. Here's a quick clean up script that will do this:

USE TestA;
GO 

IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'Jimmy')
  DROP USER Jimmy;
GO 

USE TestB;
GO 

IF EXISTS(SELECT name FROM sys.database_principals WHERE name = 'Jimmy')
  DROP USER Jimmy;
GO 

USE master;
GO 

IF EXISTS (SELECT name FROM sys.server_principals WHERE name = 'Jimmy')
  DROP LOGIN Jimmy;
GO

And once Jimmy is cleaned up, let's re-add him with our one cloning stored procedure:

EXEC DBAWork.dbo.CloneLoginAndAllDBPerms
  @NewLogin = 'Jimmy',
  @NewLoginPwd = 'SomeOtherPassw0rd!',
  @LoginToClone = 'Bobby',
  @WindowsLogin = 'F';

Now, if we check with a few test queries, we see that Jimmy has been handled as a login:

Jimm's server-level permissions

Has been granted the appropriate memberships in permissions in the TestA database:

Jimmy's permissions in TestA

And is set up in TestB database as well:

Jimmy's permissions in TestB
Next Steps


Last Updated: 2015-06-04


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, October 28, 2016 - 7:02:09 PM - jeff_yao Back To Top

@Brain Kelley,

It seems you are somehow missing script DBAWork.dbo.CreateUserInDB as well?


Friday, June 05, 2015 - 11:10:26 AM - G Peterson Back To Top

Thanks a lot! Works fine!


Friday, June 05, 2015 - 10:54:25 AM - K. Brian Kelley Back To Top

I apologize, but I accidentally left this out of the article. I will check with the administrators to get it added:

 

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 
 

Friday, June 05, 2015 - 10:19:34 AM - G Peterson Back To Top

Really useful tip, but I can't seem to find the code for sp DBAWork.dbo.CloneDBPerms in your tip?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools