Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions

As we move into new locations such as offices, apartments, or houses, we need to have the new keys given to us. Obviously, we do not want the keys for our old locations to work on the new ones. Unlike relocations in real life, we will want the SQL Servers authenticated logins and Windows authenticated logins to work on the new servers as we migrate the databases. Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. The repetitive task of scripting each one out and re-creating them one by one on the new server and re-adding the logins to the server roles and granting them server level permissions will be a royal pain. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

Yes! It is and it can be done.

First, let’s review the fundamentals of logins. Access to the server is granted via logins. There are two main categories of logins:

Windows authenticated logins: These logins can either be logins mapped to Windows users or groups. These logins are integrated with the Active Directory. Here’s the example script to create a Windows authenticated login:

CREATE LOGIN [DOMAINLOGINNAME] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

SQL Server authenticated logins: These logins are not based on Windows. They are maintained within SQL Server. Here’s the example script to create a SQL Server authenticated login:

CREATE LOGIN [SQL_LOGIN] WITH PASSWORD = 0x0200C612A516C8C41A5C6F642848AB8E3B809EB6917F277AF4C04951065FBF03FDF7F6364F1AF56D4C846FE2593F922865FF0D99C6D91D09A411F1F28FD368B2798C8F968149 HASHED, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Here are the system functions, stored procedures, tables and case expressions that you need to know to get started:

• SUSER_ID(): Returns the login identification number of the user.
• CONVERT(): Converts an expression of one data type to another. In this case, we will use this function to convert the SQL Server login password from binary data type to variable-length Unicode string data.
• QUOTENAME(): Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
• SERVER_PRINCIPALS: Contains a row for every server-level principal. It contains the important data columns such as the login name, login type, default database, and default language.
• SQL_LOGINS: Returns one row for every SQL Server authentication login. It contains the critical database columns such as the password_hash, is_expiration_checked, and is_policy_checked.
• SERVER_ROLE_MEMBERS: Returns one row for each member of each fixed and user-defined server role. Note that SQL Server 2012 and later versions have the user-defined server roles.
• SERVER_PERMISSIONS: Returns one row for each server-level permission. It contains important data columns such as state_desc for the description of permission state and permission_name for the permission name.
• COLLATE: This clause can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast. A collation specifies the rules for proper use of characters for either a language or an alphabet. Here’s we will change the column collation to SQL_Latin1_General_CP1_CI_AS.
• CASE EXPRESSION: Evaluates a list of conditions and returns one of the possible result expressions. In this case, we will use the search CASE expression.

Before running the script, you need to be aware that some the SQL Server authenticated logins with their password scripted out will exceed the default data column characters limitation of 256. You will need to change your results to display more characters in the query result to prevent the results being truncated. You can change it by going to the “Tools menu > Options > Query Results > SQL Server > Results to Text” to increase the maximum number or characters returned to 8192 – the maximum.

Here’s the script:

-- Scripting Out the Logins, Server Role Assignments, and Server Permissions
-- ************************************************************************************************************************
-- Copyright © 2015 by JP Chen of DatAvail Corporation
-- This script is free for non-commercial purposes with no warranties. 

-- CRITICAL NOTE: You’ll need to change your results to display more characters in the query result.
-- Under Tools –> Options –> Query Results –> SQL Server –> Results to Text to increase the maximum number of characters 
-- returned to 8192 the maximum or to a number high enough to prevent the results being truncated.
-- ************************************************************************************************************************
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

After you have migrated all the required logins over to the destination server, you should check for orphaned users – the database users without associated logins. You can resolve them by mapping them to valid logins. You can report and fix them by using the sp_change_users_login stored proc.