How to clone SQL Server Logins, Users, Roles and Permissions

The focus of this post borrows heavily from the work of K. Brian Kelley on mssqltips.com. I’ve consolidated Brian’s excellent 3 part tutorial into a single executable script and made changes to the original works so that the output is a table of executable T-SQL commands instead of executing the commands directly against the instance. I’ve also applied a few fixes to stop errors being thrown when applying database permissions.

The script below creates 5 Stored Procedures to facilitate the cloning of logins, users, roles and permissions with the Stored Procedure CloneLoginAndAllDBPerms acting as the master to the other four. Each Stored Procedure can also be executed independently if all that is necessary is to clone a login or a user etc.

The five stored procedures in the order they run is as follows:

1. CloneLogin
2. CreateUserInDB
3. GrantUserRoleMembership
4. CloneDBPerms
5. CloneLoginAndAllDBPerms

To deploy the stored procedures just copy the main script below, paste it into an open query window in SSMS and change the DATABASE_PLACEHOLDER_NAME to the name of the database you want the Stored Procedures to be deployed against.

Here is a example of how to execute the master Stored Procedure.

Note: For @WindowsLogin, the value F or T is used to indicate whether it is a SQL Server or Windows Login being created. Providing the @DatabaseName Parameter with a single database name will limit the T-SQL commands returned for that database only.

EXEC [dbo].[CloneLoginAndAllDBPerms] @NewLogin = TheNewGuy
	,@NewLoginPwd = N'TheNewGuyPw'
	,@WindowsLogin = N'F'
	,@LoginToClone = TestLogin
	,@DatabaseName = NULL

/*
THERE ARE 5 SPs AS PART OF THIS PROCESS, ONE BEING THE MASTER SP TO CALL THE OTHER FOUR

[1] CloneLogin
[2] CloneDBPerms
[3] CreateUserInDB
[4] GrantUserRoleMembership
[5] CloneLoginAndAllDBPerms

To deploy find and replace DATABASE_PLACEHOLDER_NAME with a utility database name.
*/
USE [DATABASE_PLACEHOLDER_NAME];
GO

IF OBJECT_ID('dbo.CloneLogin') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CloneLogin] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[CloneLogin] @NewLogin SYSNAME
	,@NewLoginPwd NVARCHAR(MAX)
	,@WindowsLogin CHAR(1)
	,@LoginToClone SYSNAME
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX);

	CREATE TABLE #CloneLoginScript (SqlCommand NVARCHAR(MAX));

	SET @SQL = '/' + '*' + 'BEGIN: CLONE SERVER LOGIN' + '*' + '/';

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT @SQL;

	SET @SQL = '/' + '*' + 'CREATE SERVER LOGIN' + '*' + '/';

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT @SQL;

	IF (@WindowsLogin = 'T')
	BEGIN
		SET @SQL = 'CREATE LOGIN [' + @NewLogin + '] FROM WINDOWS;'

		INSERT INTO #CloneLoginScript (SqlCommand)
		SELECT @SQL
	END
	ELSE
	BEGIN
		SET @SQL = 'CREATE LOGIN [' + @NewLogin + '] WITH PASSWORD = N''' + @NewLoginPwd + ''';';

		INSERT INTO #CloneLoginScript (SqlCommand)
		SELECT @SQL
	END

	SET @SQL = '/' + '*' + 'CLONE SERVER ROLES' + '*' + '/';

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT @SQL

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT 'EXEC sp_addsrvrolemember @loginame = ''' + @NewLogin + ''', @rolename = ''' + R.NAME + ''';' AS 'SQL'
	FROM sys.server_role_members AS RM
	JOIN sys.server_principals AS L ON RM.member_principal_id = L.principal_id
	JOIN sys.server_principals AS R ON RM.role_principal_id = R.principal_id
	WHERE L.NAME = @LoginToClone;

	IF @@ROWCOUNT = 0
	BEGIN
		SET @SQL = '/' + '*' + '---- No Server Roles To Clone' + '*' + '/';;

		INSERT INTO #CloneLoginScript (SqlCommand)
		SELECT @SQL
	END

	SET @SQL = '/' + '*' + 'CLONE SERVER PERMISSIONS' + '*' + '/';

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT @SQL;

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT [SQL]
	FROM (
		SELECT CASE P.[STATE]
				WHEN 'W'
					THEN 'USE master; GRANT ' + P.permission_name + ' TO [' + @NewLogin + '] WITH GRANT OPTION;'
				ELSE 'USE master;  ' + P.state_desc + ' ' + P.permission_name + ' TO [' + @NewLogin + '];'
				END AS [SQL]
		FROM sys.server_permissions AS P
		JOIN sys.server_principals AS L ON P.grantee_principal_id = L.principal_id
		WHERE L.NAME = @LoginToClone
			AND P.class = 100
			AND P.type <> 'COSQ'
		
		UNION ALL
		
		SELECT CASE P.[STATE]
				WHEN 'W'
					THEN 'USE master; GRANT ' + P.permission_name + ' ON LOGIN::[' + L2.NAME + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
				ELSE 'USE master; ' + P.state_desc + ' ' + P.permission_name + ' ON LOGIN::[' + L2.NAME + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
				END AS [SQL]
		FROM sys.server_permissions AS P
		JOIN sys.server_principals AS L ON P.grantee_principal_id = L.principal_id
		JOIN sys.server_principals AS L2 ON P.major_id = L2.principal_id
		WHERE L.NAME = @LoginToClone
			AND P.class = 101
		
		UNION ALL
		
		SELECT CASE P.[STATE]
				WHEN 'W'
					THEN 'USE master; GRANT ' + P.permission_name + ' ON ENDPOINT::[' + E.NAME + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
				ELSE 'USE master; ' + P.state_desc + ' ' + P.permission_name + ' ON ENDPOINT::[' + E.NAME + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
				END AS [SQL]
		FROM sys.server_permissions AS P
		JOIN sys.server_principals AS L ON P.grantee_principal_id = L.principal_id
		JOIN sys.endpoints AS E ON P.major_id = E.endpoint_id
		WHERE L.NAME = @LoginToClone
			AND P.class = 105
		) AS ServerPermission;

	IF @@ROWCOUNT = 0
	BEGIN
		SET @SQL = '/' + '*' + '---- No Server Permissions To Clone' + '*' + '/';;

		INSERT INTO #CloneLoginScript (SqlCommand)
		SELECT @SQL
	END

	SET @SQL = '/' + '*' + 'END: CLONE SERVER LOGIN' + '*' + '/';

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT @SQL;

	SELECT *
	FROM #CloneLoginScript
END;
GO

USE [DATABASE_PLACEHOLDER_NAME];
GO

IF OBJECT_ID('dbo.CreateUserInDB') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CreateUserInDB] AS SELECT 1')
GO

ALTER PROC [dbo].[CreateUserInDB] @NewLogin SYSNAME
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX);
	DECLARE @DbName SYSNAME;
	DECLARE @Database TABLE (DbName SYSNAME)

	SET @DbName = ''

	CREATE TABLE #CloneDbUserScript (SqlCommand NVARCHAR(MAX));

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT NAME
		FROM sys.databases
		WHERE state_desc = 'ONLINE'
		ORDER BY NAME ASC;
	END
	ELSE
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT @DatabaseName
	END;

	SET @SQL = '/' + '*' + 'BEGIN: CREATE DATABASE USER' + '*' + '/';

	INSERT INTO #CloneDbUserScript (SqlCommand)
	SELECT @SQL;

	WHILE @DbName IS NOT NULL
	BEGIN
		SET @DbName = (
				SELECT MIN(DbName)
				FROM @Database
				WHERE DbName > @DbName
				)
		SET @SQL = '
INSERT INTO #CloneDbUserScript (SqlCommand)
SELECT ''USE [' + @DbName + ']; 
IF EXISTS(SELECT name FROM sys.database_principals 
WHERE name = ' + '''''' + @LoginToClone + '''''' + ')
BEGIN
CREATE USER [' + @NewLogin + '] FROM LOGIN [' + @NewLogin + '];
END;''';

		EXEC (@SQL);
	END;

	IF EXISTS (
			SELECT COUNT(SqlCommand)
			FROM #CloneDbUserScript
			HAVING COUNT(SqlCommand) < 2
			)
	BEGIN
		SET @SQL = '/' + '*' + '---- No Database User To Create' + '*' + '/';;

		INSERT INTO #CloneDbUserScript (SqlCommand)
		SELECT @SQL
	END;

	SET @SQL = '/' + '*' + 'END: CREATE DATABASE USER' + '*' + '/';

	INSERT INTO #CloneDbUserScript (SqlCommand)
	SELECT @SQL;

	SELECT SqlCommand
	FROM #CloneDbUserScript;

	DROP TABLE #CloneDbUserScript;
END;
GO

USE [DATABASE_PLACEHOLDER_NAME];
GO

IF OBJECT_ID('dbo.GrantUserRoleMembership') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[GrantUserRoleMembership] AS SELECT 1')
GO

ALTER PROC dbo.GrantUserRoleMembership @NewLogin SYSNAME
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX);
	DECLARE @DbName SYSNAME;
	DECLARE @Database TABLE (DbName SYSNAME)

	SET @DbName = ''

	CREATE TABLE #CloneRoleMembershipScript (SqlCommand NVARCHAR(MAX));

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT NAME
		FROM sys.databases
		WHERE state_desc = 'ONLINE'
		ORDER BY NAME ASC;
	END
	ELSE
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT @DatabaseName
	END;

	SET @SQL = '/' + '*' + 'BEGIN: CLONE DATABASE ROLE MEMBERSHIP' + '*' + '/';

	INSERT INTO #CloneRoleMembershipScript (SqlCommand)
	SELECT @SQL;

	WHILE @DbName IS NOT NULL
	BEGIN
		SET @DbName = (
				SELECT MIN(DbName)
				FROM @Database
				WHERE DbName > @DbName
				)
		SET @SQL = '
INSERT INTO #CloneRoleMembershipScript (SqlCommand) 
SELECT ''USE [' + @DBName + ']; 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 (@SQL);
	END;

	IF EXISTS (
			SELECT COUNT(SqlCommand)
			FROM #CloneRoleMembershipScript
			HAVING COUNT(SqlCommand) < 2
			)
	BEGIN
		SET @SQL = '/' + '*' + '---- No Database Roles To Clone' + '*' + '/';;

		INSERT INTO #CloneRoleMembershipScript (SqlCommand)
		SELECT @SQL
	END;

	SET @SQL = '/' + '*' + 'END: CLONE DATABASE ROLE MEMBERSHIP' + '*' + '/';

	INSERT INTO #CloneRoleMembershipScript (SqlCommand)
	SELECT @SQL;

	SELECT SqlCommand
	FROM #CloneRoleMembershipScript;

	DROP TABLE #CloneRoleMembershipScript;
END;
GO

USE [DATABASE_PLACEHOLDER_NAME];
GO

IF OBJECT_ID('dbo.CloneDBPerms') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CloneDBPerms] AS SELECT 1')
GO

ALTER PROC dbo.CloneDBPerms @NewLogin SYSNAME
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(max);
	DECLARE @DbName SYSNAME;
	DECLARE @Database TABLE (DbName SYSNAME)

	SET @DbName = ''

	CREATE TABLE #CloneDbPermissionScript (SqlCommand NVARCHAR(MAX));

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT NAME
		FROM sys.databases
		WHERE state_desc = 'ONLINE'
		ORDER BY NAME ASC;
	END
	ELSE
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT @DatabaseName
	END;

	SET @SQL = '/' + '*' + 'BEGIN: CLONE DATABASE PERMISSIONS' + '*' + '/';

	INSERT INTO #CloneDbPermissionScript (SqlCommand)
	SELECT @SQL;

	WHILE @DbName IS NOT NULL
	BEGIN
		SET @DbName = (
				SELECT MIN(DbName)
				FROM @Database
				WHERE DbName > @DbName
				)
		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON DATABASE::[' + @DbName + '] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + 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 + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON SCHEMA::['' 
		 + S.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + 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 + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + O.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + 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
	  JOIN [' + @DbName + '].sys.schemas AS S
		ON S.schema_id = O.schema_id
	WHERE class = 1
	  AND U.name = ''' + @LoginToClone + '''
	  AND P.major_id > 0
	  AND P.minor_id = 0';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '] WITH GRANT OPTION;'' 
		 COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + 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.schemas AS S
		ON S.schema_id = O.schema_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;'

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON USER::['' 
		 + U2.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + 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 + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON SYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + 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 + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON ASYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + 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 + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON CERTIFICATE::['' 
		 + C.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + 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 + ''';';

		EXEC (@SQL)
	END;

	IF EXISTS (
			SELECT COUNT(SqlCommand)
			FROM #CloneDbPermissionScript
			HAVING COUNT(SqlCommand) < 2
			)
	BEGIN
		SET @SQL = '/' + '*' + '---- No Database Permissions To Clone' + '*' + '/';;

		INSERT INTO #CloneDbPermissionScript (SqlCommand)
		SELECT @SQL
	END;

	SET @SQL = '/' + '*' + 'END: CLONE DATABASE PERMISSIONS' + '*' + '/';

	INSERT INTO #CloneDbPermissionScript (SqlCommand)
	SELECT @SQL;

	SELECT SqlCommand
	FROM #CloneDbPermissionScript;

	DROP TABLE #CloneDbPermissionScript;
END;
GO

USE [DATABASE_PLACEHOLDER_NAME];
GO

IF OBJECT_ID('dbo.CloneLoginAndAllDBPerms') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CloneLoginAndAllDBPerms] AS SELECT 1')
GO

ALTER PROC dbo.CloneLoginAndAllDBPerms @NewLogin SYSNAME
	,@NewLoginPwd NVARCHAR(MAX)
	,@WindowsLogin CHAR(1)
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	CREATE TABLE #CloneLoginAndAllDBPermsScript (SqlCommand NVARCHAR(MAX));

	INSERT INTO #CloneLoginAndAllDBPermsScript
	EXEC DATABASE_PLACEHOLDER_NAME.dbo.CloneLogin @NewLogin = @NewLogin
		,@NewLoginPwd = @NewLoginPwd
		,@WindowsLogin = @WindowsLogin
		,@LoginToClone = @LoginToClone;

	INSERT INTO #CloneLoginAndAllDBPermsScript
	EXEC DATABASE_PLACEHOLDER_NAME.dbo.CreateUserInDB @NewLogin = @NewLogin
		,@LoginToClone = @LoginToClone
		,@DatabaseName = @DatabaseName;

	INSERT INTO #CloneLoginAndAllDBPermsScript
	EXEC DATABASE_PLACEHOLDER_NAME.dbo.GrantUserRoleMembership @NewLogin = @NewLogin
		,@LoginToClone = @LoginToClone
		,@DatabaseName = @DatabaseName;

	INSERT INTO #CloneLoginAndAllDBPermsScript
	EXEC DATABASE_PLACEHOLDER_NAME.dbo.CloneDBPerms @NewLogin = @NewLogin
		,@LoginToClone = @LoginToClone
		,@DatabaseName = @DatabaseName;

	SELECT SqlCommand
	FROM #CloneLoginAndAllDBPermsScript

	DROP TABLE #CloneLoginAndAllDBPermsScript
END;
GO

8 thoughts on “How to clone SQL Server Logins, Users, Roles and Permissions”

Arun N says:

February 18, 2020 at 3:08 am

Very good article. Found this at the right time and made my work a lot easier.
Thank you very much.

LikeLike

1. bloggins86 says:
  
  February 20, 2020 at 2:12 pm
  
  Glad I could help, thanks for the comment.
  
  LikeLike
  
Sreeni says:

July 26, 2020 at 4:53 am

Thank you very much.. It will ease our work .. Once again Thank you very much.

LikeLike

1. bloggins86 says:
  
  August 2, 2020 at 3:26 pm
  
  Very glad I could help. Thanks for the comment.
  
  LikeLike
  
Sreeni says:

July 26, 2020 at 4:56 am

I will share this post to my entire team. It’s been several years suffered to find the best one for me. 🙂

LikeLike

Pingback: Configuring SQL Logins for SCOM Accounts Made Easy - The Monitoring Guys
Ashutosh says:

July 2, 2021 at 7:40 am

Thanks a lot for this script. It saved my lot of time.

LikeLike

Desmond Chew says:

August 15, 2022 at 3:10 am

I tried to clone a sql server account to windows authenticationed login, setting
,@WindowsLogin = N’T’. What should I set for ,@NewLoginPwd = N’TheNewGuyPw’ since the password is AD authenticated?

LikeLike

Tidbytez

Delivering Bite-Sized Tech Knowledge

8 thoughts on “How to clone SQL Server Logins, Users, Roles and Permissions”

Leave a Reply Cancel reply

Share this:

Like this:

Related

8 thoughts on “How to clone SQL Server Logins, Users, Roles and Permissions”

Leave a Reply Cancel reply