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.
Brian Kelley is an author, columnist, Certified Information Systems Auditor (CISA), and former Microsoft Data Platform (SQL Server) MVP (2009-2016) focusing primarily on SQL Server and Windows security. Brian currently serves as a data architect as well as an independent infrastructure/security architect concentrating on Active Directory, SQL Server, and Windows Server. He has served in a myriad of other positions including senior database administrator, data warehouse architect, web developer, incident response team lead, and project manager. Brian has spoken at 24 Hours of PASS, IT/Dev Connections, SQLConnections, the Techno Security and Forensics Investigation Conference, the IT GRC Forum, SyntaxCon, and at various SQL Saturdays, Code Camps, and user groups.
- MSSQLTips Awards: Author of the Year Contender – 2015, 2017 | Champion (100+ tips) – 2014
