How to Script Login and User Permissions in 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
