How to Script Login and User Permissions in SQL Server

http://udayarumilli.com/script-login-user-permissions-sql-server/

 

 

Migrating login and user permissions to a new instance is one of the most common tasks for a SQL DBA. This post “How to Script Login and User Permissions in SQL Server“ can help you in scripting the login and the user permissions for a given database. Most of us knew that there is a script “sp_help_revlogin” available for migrating SQL Server logins which can also handle the passwords for SQL Server logins. Just to summarize the topic we are also giving the MS suggested script.

Server / Instance Level

• Script Logins with Passwords
• Script Login Server Roles
• Script the Server Level Permissions

Database / Object Level

• Script User Creation
• Script User Database Roles
• Script the Database Level Permissions
• Script Object Level Permission

 

Here is the script for generating Login creation script for the given SQL Server instance. Here is the reference from the MS site. https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

There is another beautiful script provided by JP Chen and it also working for login migration.

Server / Instance Level:

--https://www.datavail.com/blog/scripting-out-the-logins-server-role-assignments-and-server-permissions/
/********************************************************************************************************************/
-- Scripting Out the Logins, Server Role Assignments, and Server Permissions
/********************************************************************************************************************/
SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
			   CASE 
					WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
						+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
					ELSE ' FROM WINDOWS WITH'
				END 
	   +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
		ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
		AND SP.name NOT LIKE '##%##'
		AND SP.name NOT LIKE 'NT AUTHORITY%'
		AND SP.name NOT LIKE 'NT SERVICE%'
		AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
	JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
	JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
		AND SL.name NOT LIKE '##%##'
		AND SL.name NOT LIKE 'NT AUTHORITY%'
		AND SL.name NOT LIKE 'NT SERVICE%'
		AND SL.name <> ('sa');


-- Scripting out the Permissions to Be Granted
SELECT 
	CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
		THEN SrvPerm.state_desc 
		ELSE 'GRANT' 
	END
    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
	CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
		THEN '' 
		ELSE ' WITH GRANT OPTION' 
	END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
	JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE   SP.type IN ( 'S', 'U', 'G' ) 
		AND SP.name NOT LIKE '##%##'
		AND SP.name NOT LIKE 'NT AUTHORITY%'
		AND SP.name NOT LIKE 'NT SERVICE%'
		AND SP.name <> ('sa');

SET NOCOUNT OFF

 

Database / Object Level:

USE Master  -- Use the required database name here
GO
SET NOCOUNT ON;

PRINT 'USE ['+DB_NAME()+']';
PRINT 'GO'

/********************************************************************************/
/**************** Create a new user and map it with login ***********************/
/********************************************************************************/

PRINT '/*************************************************************/'
PRINT '/************** Create User Script ***************************/'
PRINT '/*************************************************************/'

SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']' 
FROM sys.database_principals
WHERE	[Type] IN ('U','S')
		AND 
		[NAME] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA')

GO
-- Troubleshooting User creation issues
PRINT '/***'+CHAR(10)+
'--Error 15023: User or role <XXXX> is already exists in the database.'+CHAR(10)+
'--Then Execute the below code can fix the issue'+CHAR(10)+
'EXEC sp_change_users_login ''Auto_Fix'',''<Failed User>'''+CHAR(10)+
'GO **/'

/************************************************************************/
/************  Script the User Role Information *************************/
/************************************************************************/

PRINT '/**********************************************************/'
PRINT '/************** Create User-Role Script *******************/'
PRINT '/**********************************************************/'

SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBP.NAME + '''' 
FROM sys.database_principals DBP
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBP.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id
WHERE DBP.NAME <> 'dbo'

GO

/***************************************************************************/
/************  Script Database Level Permission ****************************/
/***************************************************************************/

PRINT '/*************************************************************/'
PRINT '/************** Database Level Permission ********************/'
PRINT '/*************************************************************/'

SELECT	CASE WHEN DBP.state <> 'W' THEN DBP.state_desc ELSE 'GRANT' END
		+ SPACE(1) + DBP.permission_name + SPACE(1)
		+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USR.name) COLLATE database_default
		+ CASE WHEN DBP.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';' 
FROM	sys.database_permissions AS DBP
		INNER JOIN	sys.database_principals AS USR	ON DBP.grantee_principal_id = USR.principal_id
WHERE	DBP.major_id = 0 and USR.name <> 'dbo'
ORDER BY DBP.permission_name ASC, DBP.state_desc ASC


/***************************************************************************/
/************  Script Object Level Permission ******************************/
/***************************************************************************/

PRINT '/*************************************************************/'
PRINT '/************** Object Level Permission **********************/'
PRINT '/*************************************************************/'

SELECT	CASE WHEN DBP.state <> 'W' THEN DBP.state_desc ELSE 'GRANT' END
		+ SPACE(1) + DBP.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(OBJ.schema_id)) + '.' + QUOTENAME(OBJ.name) 
		+ CASE WHEN CL.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(CL.name) + ')' END
		+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USR.name) COLLATE database_default
		+ CASE WHEN DBP.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';' 
FROM	sys.database_permissions AS DBP
		INNER JOIN	sys.objects AS OBJ	ON DBP.major_id = OBJ.[object_id]
		INNER JOIN	sys.database_principals AS USR	ON DBP.grantee_principal_id = USR.principal_id
		LEFT JOIN	sys.columns AS CL	ON CL.column_id = DBP.minor_id AND CL.[object_id] = DBP.major_id
ORDER BY DBP.permission_name ASC, DBP.state_desc ASC



SET NOCOUNT OFF;

 

Summary:

The post “How to Script Login and User Permissions in SQL Server” can help you in transferring logins and users between SQL Server instances. Please do comment on the below comment box if you find any issues in code.

Download Scripts:

How to Script Login and User Permissions in SQL Server_Logins

How to Script Login and User Permissions in SQL Server_Users