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

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:

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

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:

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:

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

And is set up in TestB database as well:

Next Steps

• Review the first tip in the series on cloning SQL Server logins.
• Review the second tip on the scripts for cloning SQL Server 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.