Press "Enter" to skip to content

SQL Server: How to Script Login and User Permissions in SQL Server

0

Database migration task will always be associated with the transfer of logins and database user permissions to a new instance. This blog post “How to Script Login and User Permissions in SQL Server“ will help you in scripting the login and user permissions for a given database. We all are aware and would have encountered with the script “sp_help_revlogin” from Microsoft available for migrating SQL Server logins with passwords for SQL authenticated logins. Sharing the Microsoft login migration script as well.

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

Server / Instance Level:

— 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 YourDBName — 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:

This blog post “How to Script Login and User Permissions in SQL Server” will help DBA’s in  transferring instance specific logins and database specific users between SQL Server instances. Please do comment with your views and suggestions on this post.

Leave a Reply

Your email address will not be published. Required fields are marked *