Tag Archives: copy

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

June 2, 2018Database Administrator, dba, How To, sql, SQL Server, T-SQL, tsqlclone, copy, guide, How To, howto, lesson, login, permissions, replicate, role, SQL Server, tutorial, userTidbytez

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

How to copy a large result set from SQL Server Management Studio to Excel

January 16, 2018Data, Database Administrator, dba, Excel, How To, Reporting, SQL Server, T-SQLcopy, data set, Excel, Execption of type 'System.OutOfMemoryException' was thrown, from, guide, How To, howto, lesson, management studio, ntile, paste, Query, results, section, segment, split, SQL Server, ssms, tutorialTidbytez 1

So you’ve tried copying and pasting the results of a query into an excel file only to get the out of memory exception. Now the fun starts!

There are multiple options to achieve copying data from SQL Server Management Studio to excel however most of them are a pain.

You’ve tried the save as option but the csv and text files have jumbled up content. You could use the Management Studio export function, but this is intended for physical tables not results sets and you might not have the permissions to create tables in the environment. You could use the bcp Utility but you’ve probably read leaving this option turned on represents a security risk. You could create an SSIS package . . . yeah that’s an efficient and effective option when you just want the damn results of an ad hoc query!!!

I’d suggest splitting the result set into chunks. You can then copy and paste the chunks into the excel file without running out of memory. Sure it’s kinda manual but trust me it’ll take less time than the options above.

I’d wager you probably only need the result set split into two, so you’ve to copy and paste twice rather than once. Not that big a deal right? I’ve even provided some code below that will really move things along.

Start by writing your query results into a temporary tablet called #QueryResult, for example SELECT * INTO #QueryResult FROM TableName.

Then all you need to do is determine how many segments you need. NTILE(n) is a function that allocates your output into n segments, each of the same size (give or take rounding when the number of rows isn’t divisible by n).

So this produces an output like:

Id	Name	Ntile
1	Mickey	1
2	Leo	1
3	Raph	2
4	Donnie	2

Start by leaving n set to the default of 2. Once the data is written to the table #QueryResult run the code below in the same SSMS window the temp table was created in. Running the code should produce the same number of returned result sets as the n value you provided. Use a higher n number to create more segments if you still run out of memory when you try to copy and paste the first segment.

/*
Write your query results to a temp table here
i.e. SELECT * INTO #QueryResult FROM TableName
*/
DECLARE @n INT
DECLARE @i INT

/*
Set n to how many segments/results set returned you need
*/
SET @n = 2
SET @i = 1

SELECT *
	,NTILE(@n) OVER (
		ORDER BY RowNum
		) AS NtileGroup
INTO #Export
FROM (
	SELECT ROW_NUMBER() OVER (
			ORDER BY (
					SELECT NULL
					)
			) AS RowNum
		,*
	FROM #QueryResult
	) AS a

WHILE @i <= @n
BEGIN
	SELECT *
	FROM #Export
	WHERE NtileGroup = @i
	ORDER BY RowNum ASC

	SET @i = @i + 1
END

DROP TABLE #QueryResult

DROP TABLE #Export

So that’s it, you should now be able to copy and paste your results. Maybe someday in the future Microsoft will add the option of saving results directly to excel . . .

How to copy only files from a folder and respective subfolders using a batch script

February 26, 2015Batch Script, How ToBatch, Batch script to copy only files from a folder and respective subfolders, cmd, copy, files, folders, guide, How To, lesson, only files, subfolders, tutorialTidbytez

Ok so here’s a nifty little batch script that will loop through all the directories in a directory to gather all the subsequent files into a new directory. (Code at bottom of the page)

(For those of you that don’t know what a batch script is here’s a quick definition. A batch file is a text file that contains a sequence of commands for a computer operating system. It’s called a batch file because it batches (bundles or packages) into a single file a set of commands that would otherwise have to be presented to the system interactively from a keyboard one at a time.)

Here’s a diagram explaining how the batch script provided at the bottom of the page works. (Note that the folders aren’t copied just the files.)

So why would you use this?

Lets say you’re dealing with a system that has outputted thousands of folders into a single folder and each folder contains files.

You could be looking at millions of files to be gathered, a process that cannot be done manually.

Or a simpler use case might be you’ve downloaded thousands of movies and each was put into a separate folder. Now you just want the media files in one location.

How to use:

Open the program notepad.

Copy the code below and past it into notepad saving the file with the extension .bat

Place the batch file in the parent folder, i.e. the folder all the other folders are in.

Double click the batch file.

The batch file will create a folder one directory level above where the parent folder is located and copy all the files to this location.

An example of how to use the file is as follows:

If you had the parent folder “My Movies” on your desktop.

Pasting the batch file into this folder, and double clicking on it, would copy every movie into a folder called “Copied from My Movies” on your desktop.

And here’s the code,
Adiós.

	@ECHO OFF
	TITLE WAIT !
	:: ASSIGN THE FILE PATH OF BATCH FILE TO A VARIABLE
	SET "sourceDir=%CD%"
	:: GET THE NAME OF THE FOLDER WHICH THE BATCH FILE IS IN
	FOR %%a IN (.) DO SET currentFolder=%%~na
	:: GO UP ONE DIRECTORY
	CD ..
	:: MAKE A DYNAMIC FOLDER NAME
	::SET folderName=Copied From %currentFolder%
	SET "folderName=Copied from %currentFolder%"
	:: CREATE A FOLDER TO PUT THE COPIED FILES IN
	:: IF FOLDER ALREADY EXISTS DELETE IT
	IF EXIST "%folderName%" RMDIR "%folderName%" /S /Q
	MKDIR "%folderName%"
	:: ASSIGN DESTINATION FOLDER TO A VARIABLE
	SET "destinationFolder=%CD%\%folderName%"
	:: CREATE A LOG FILE IN DESTINATION FOLDER
	SET "_report=%destinationFolder%\logxcopy.txt"
	:: CREATE ERROR MESSAGE
	IF NOT EXIST "%sourceDir%" (ECHO.Could not find %sourceDir% &GoTo:DONE)
	:: OVERWRITE PREVIOUS LOG
	>"%_report%" (
	echo.%date% – %time%
	echo.—————————————————
	echo.
	)
	:: COPY FILES
	FOR /F "Delims=" %%! IN ('DIR "%sourceDir%\" /b /s /a-d 2^>NUL') DO (
	@ECHO.%%! &(
	@XCOPY "%%!" "%destinationFolder%\" /i /y /h /f /c >>"%_report%",2>&1)
	)
	:DONE
	TITLE,Done…….
	ECHO.&PAUSE>NUL

view raw

batch copy file

hosted with ❤ by GitHub

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: