Tag Archives: user

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 create a list of all users of all databases in a SQL Server instance

February 5, 2018Data, Database Administrator, dba, How To, sql, SQL Server, T-SQLaccess, data protection, database, every, GDPR, guide, How To, How to create a list of all users of all databases in a SQL Server instance, howto, instance, lesson, login, matrix, script, secutiry, tutorial, userTidbytez 1

Running the T-Sql script below will return a list of essentially all users from all databases in an instance. This is a particularly useful script if you are assessing the back end security matrix for a company. You may want to do this ahead of GDPR coming into effect to determine if your company is compliant . Data for the following fields are provided.

ServerName
DbName
UserName
LoginType
Permission
StateOf
AccessLevel
ObjectName

SET NOCOUNT ON;

DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @DbName AS SYSNAME;
DECLARE @sql AS VARCHAR(MAX);
DECLARE @ServerName AS SYSNAME;

SET @ServerName = (
		SELECT @@SERVERNAME
		);

IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
	DROP TABLE #User
END;

CREATE TABLE #User (
	ServerName SYSNAME
	,DbName SYSNAME
	,UserName SYSNAME NULL
	,LoginType VARCHAR(255) NULL
	,Permission VARCHAR(255) NULL
	,StateOf VARCHAR(255) NULL
	,AccessLevel VARCHAR(255) NULL
	,ObjectName SYSNAME NULL
	);

SET @DbName = '';

INSERT INTO @Database (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC;

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			)
	/*
	PUT CODE HERE
	*/
	SET @sql = '
INSERT INTO #User (
	ServerName
	,DbName
	,UserName
	,LoginType
	,Permission
	,StateOf
	,AccessLevel
	,ObjectName
	)
SELECT ''' + @ServerName + ''' AS ServerName
	,''' + @DbName + ''' AS DbName
	,princ.name AS UserName
	,princ.type_desc AS LoginType
	,perm.permission_name AS Permission
	,perm.state_desc AS StateOf
	,perm.class_desc AS AccessLevel
	,object_name(perm.major_id) AS ObjectName
FROM ' + QUOTENAME(@DbName) + '.sys.database_principals princ
LEFT JOIN ' + QUOTENAME(@DbName) + '.sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id
'

	EXEC (@sql)
END;

SELECT *
FROM #User;

DROP TABLE #User;

Server name is included in case you’d like to run this script across multiple environments. The results of each server can be combined in an excel file more easily.

DbName and UserName are self explanatory.

LoginType, whether the login is via Windows or SQL Server.

Permission, what actions the user can perform e.g. SELECT, UPDATE etc.

StateOf, whether permission to the object has been granted or denied.

AccessLevel, whether the user has access to the entire database or is restricted to objects etc. though it is good practice that a user should have locked down access. From a GDPR perspective it would be hard to make a case for allowing a user to have access to an entire database instead of a few columns, views or stored procedures.

ObjectName, if the user only has restricted access to specific objects this script will list them.

How to identify databases with Guest user enabled

November 8, 2016Database Administrator, dba, sql, SQL Server, T-SQLenabled, guest, guide, How To, How to identify databases with Guest user enabled, howto, identify, lesson, Microsoft, RAP, Risk Assessment Program, SQL Server, tutorial, userTidbytez 1

As best practice it is recommended to disable guest user in every user database, i.e. not master, msdb and tempdb, to improve the security of SQL Server. Guest user permits access to a database for any logins that are not mapped to a specific database user. The guest user cannot be dropped but it can be disabled by revoking the CONNECT permission.

Use the script below to identify which databases have guest user enabled.

USE master;
GO

DECLARE @database_name SYSNAME
	,@sqlcmd NVARCHAR(4000)

DECLARE databases_cursor CURSOR
FOR
SELECT NAME
FROM sys.databases
WHERE STATE IN (0)
	AND database_id > 4
ORDER BY NAME

CREATE TABLE #guest_users_enabled (
	database_name SYSNAME
	,user_name SYSNAME
	,permission_name NVARCHAR(128)
	,state_desc NVARCHAR(6)
	)

OPEN databases_cursor;

FETCH NEXT
FROM databases_cursor
INTO @database_name;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sqlcmd = N'use ' + @database_name + ';

        insert into #guest_users_enabled

        SELECT ''' + @database_name + ''' as database_name, name,

        permission_name, state_desc

        FROM sys.database_principals dpr

        INNER JOIN sys.database_permissions dpe

        ON dpr.principal_id = dpe.grantee_principal_id

        WHERE name = ''guest'' AND permission_name = ''CONNECT'''

	EXEC sp_executesql @sqlcmd

	FETCH NEXT
	FROM databases_cursor
	INTO @database_name;
END

SELECT database_name
	,user_name
	,permission_name
	,state_desc
FROM #guest_users_enabled
ORDER BY database_name ASC

DROP TABLE #guest_users_enabled

CLOSE databases_cursor;

DEALLOCATE databases_cursor;
GO

Use the script below, changing the placeholder database with the database identified in the script above, to revoke permission for the guest user to connect to that database.

USE [database name];

GO

REVOKE CONNECT FROM GUEST;

GO

How to drop a user from a SQL Server database when you encounter the error message “The database principal owns a schema in the database, and cannot be dropped”

November 3, 2016Database Administrator, dba, sql, SQL Server, T-SQLbe, cannot, drop, drop user, How To, lesson, owner, owns, principal, schema, The database principal owns a schema in the database, and cannot be dropped, userTidbytez 1

So if you have encountered the error above “The database principal owns a schema in the database, and cannot be dropped” you will not be able to drop the user until ownership of the effected schema has been transferred to another user/role. In order to drop the user, you have to find the schema that is assigned first. You can do this by running the script below replacing myUser with the user name in question.

SELECT name 
FROM  sys.schemas 
WHERE principal_id = USER_ID(‘myUser’)

Then, use the schema found from the above query in place of the SchemaName below. This transfers ownership to dbo. You may need to alter authorization for multiple schema. Just run the statement for each returned schema replacing SchemaName. You can then drop your user.

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo;
 
GO

DROP USER myUser;

How to identify all user objects in the SQL Server Master database

October 21, 2016sql, SQL Server, T-SQLcreated, find, find objects master database, guide, How To, How to identify all user objects in the SQL Server Master database, howto, identify, lesson, objects, objects master database, script, should not be, tutorial, unwanted objects master database, used for, user, user database, what isTidbytez 1

What is the SQL Server Master Database used for?

The master database is used by SQL Server to contain all of the system level information:

Logins
Linked servers
Endpoints
Configuration settings
Information about the other databases on this instance and the location of their files

If the master database is not present, SQL Server cannot start.

This is way you should always take regular backups of the master database.
as if SQL Server suffers a failure, those changes will be lost and you’ll be in a lot of trouble.

So the master database is needed for SQL Server to work. Logic and objects needed to enable your application should not be tangled up in there so you should always avoid creating objects in the master database. Use a user database instead.

In saying that everyone has made the mistake of not specifying the database they want to USE in a script and by default objects get written to the Master database. Or you might have inherited the responsibility for an application which did not conform to best practices and it uses objects created in Master.

To gain visibility of these objects just run the script below.

SELECT o.object_id AS ObjectId
	,o.NAME AS ObjectName
	,o.type_desc AS ObjectType
	,o.create_date AS CreateDate
	,o.modify_date AS ModifyDate
	,SUM(st.row_count) AS RowCnt
	,CAST(SUM(st.used_page_count) / 128.0 AS DECIMAL(36, 1)) AS DataSize_MB
FROM master.sys.objects o
LEFT JOIN master.sys.dm_db_partition_stats st ON st.object_id = o.object_id
	AND st.index_id < 2
GROUP BY o.object_id
	,o.NAME
	,o.type_desc
	,o.create_date
	,o.modify_date
	,o.is_ms_shipped
HAVING o.is_ms_shipped = 0
	AND o.NAME <> 'sp_ssis_startup'
	AND o.type_desc NOT LIKE '%CONSTRAINT%'
ORDER BY CAST(SUM(st.used_page_count) / 128.0 AS DECIMAL(36, 1)) DESC
	,RowCnt DESC

Report Requirements Template

May 16, 2015Reporting, SQL Server, T-SQLDeveloper, domain, Excel, knowledge, Reporting, Reporting Requirements Template, Requirements, T-SQL, Template, userTidbytez

When a developer is asked to create a business report it always involves a meeting of two disciplines.

The developer has the technical knowledge of pulling raw data, manipulating it and presenting interpreted data as information to be consumed by an end user. They may also know the location and source of the data to be used and how regularly this source is refreshed or updated with new data.

The Requester of the report will usually be the person with business domain knowledge. They understand the business rules which need to be applied to the data to transform it into information.

An obvious example for this is the relationship between a SQL developer working in an accounting environment. The developer is not an account and the account is not a developer but the two need to be able to collaborate and communicate to ensure that a report eventually outputs the correct information at the require period reliably.

Failure in achieving this goal can have disastrous consequences. For example projecting further revenue based on faulty data.

A good start for creating a foundation for effective communication and collaboration is for the developer and requester to step through a requirements template. This gives the report a structure and focus while also serving as a means of documenting the creation of the report from a business perspective and a future resource from a development perspective, i.e. the requirement of a future developer new to the report updating and modifying the report.

Attached is an example Template.

The Requirements sheet deals with questions like:

Who is the requester?

Why is the report needed?

What information will the report return?

This sheet will primarily be filled in by the requester.

The Report Fields sheet tries to plug the gap in knowledge between the requester and the developer by exploring what is the information required and what fields, calculations correspond to this information. The developer and requester may need to step through this sheet together to ensure their intended outcomes align.

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: