Migrating Logins: All Logins Welcome

Tim Ford login, migrate, script, SQL 0 Comment Oct 23, 2013

Last week I had the pleasure to present on the topic of Dynamic Management Objects at the Professional Association for SQL Server (PASS) Summit in Charlotte, NC. I also had the opportunity to learn from some of the brightest and most-talented professionals in the SQL Server community. I don’t recall if this question was posed in my session or at the end of Jes Borland’s session on SQL Agent (one of those aforementioned “brightest” and “most-talented”) but it was in one of those sessions – and has nothing to do with either topic.

It has to do with migrating logins between servers.

Michal Sadowski approached the podium, requested one of my business cards, and created a blog post in the process. Thanks Michal, this post is for you!

In the script below I separate the process into four separate actions. Each of which I go into detail through the remainder of the post. If you’re impatient though here is the script. Jump right in. It’s pretty intuitive if you’re already familiar with moving logins between servers. I am going on the basis that we’re dealing with versions of SQL Server created in the current millennium. These scripts won’t work if you’re messing around with your grandpa’s SQL Servers from 2000 or older.

All Logins Are Not The Same – This Script Provides Login Equality

--=================================================================
-- SP_HELP_REVLOGIN COMMANDS AND DEFAULT DB CREATE USER COMMANDS
--=================================================================
 
SELECT SP.[principal_id]
	, SP.[name]
	, SP.[default_database_name]
	, 'EXEC [sp_help_revlogin] ' + '''' + SP.name + '''' + ';' AS script_command
	, 'USE [master]; CREATE USER [' + SP.name + '] FROM LOGIN [' + SP.name + '];' AS user_command 
FROM master.sys.[server_principals] SP 
WHERE SP.[TYPE] = 'S'
	AND SP.name != 'sa'
	AND SP.name NOT LIKE ('#%')
ORDER BY SP.[name];
 
--=================================================================
-- AD GROUPS CREATE LOGINS AND DEFAULT DB USER COMMANDS 
--=================================================================
 
SELECT SP.[principal_id]
	, SP.[name]
	, SP.[default_database_name]
	, 'CREATE LOGIN [' + SP.name + '] FROM WINDOWS WITH DEFAULT_DATABASE = [master];' AS login_command 
	, 'USE [master]; CREATE USER [' + SP.name + '] FROM LOGIN [' + SP.name + '];' AS user_command 
FROM master.sys.[server_principals] SP 
WHERE SP.[TYPE] = 'G'
	AND SP.name NOT LIKE 'NT %'
ORDER BY SP.[name];
 
--=================================================================
-- AD LOGINS CREATE LOGINS AND DEFAULT DB USER COMMANDS 
--=================================================================
 
SELECT SP.[principal_id]
	, SP.[name]
	, SP.[default_database_name]
	, 'CREATE LOGIN [' + SP.name + '] FROM WINDOWS WITH DEFAULT_DATABASE = [master];' AS login_command 
	, 'USE [master]; CREATE USER [' + SP.name + '] FROM LOGIN [' + SP.name + '] WITH DEFAULT_SCHEMA=[dbo];' AS user_command 
FROM master.sys.[server_principals] SP 
WHERE SP.[TYPE] = 'U'
	AND SP.name NOT LIKE 'NT %'
ORDER BY SP.[name];
 
--=================================================================
-- SERVER ROLE MEMBERS
--=================================================================
 
SELECT R.name AS server_role
	, P.name AS role_member
	, 'EXEC master..sp_addsrvrolemember N' + '''' + P.name + '''' + ', N' + '''' + R.name + '''' + ';' AS command
FROM sys.server_role_members RM 
	INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_id
	INNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE') R
		ON RM.role_principal_id = R.principal_id
WHERE P.name NOT LIKE '#%'
	AND P.name NOT LIKE 'NT %'
	AND P.type_desc &lt;&gt; 'SERVER_ROLE'
	AND P.name NOT IN ('sa')
ORDER BY R.[name], P.[name];

--================================================================= -- SP_HELP_REVLOGIN COMMANDS AND DEFAULT DB CREATE USER COMMANDS --================================================================= SELECT SP.[principal_id] , SP.[name] , SP.[default_database_name] , 'EXEC [sp_help_revlogin] ' + '''' + SP.name + '''' + ';' AS script_command , 'USE [master]; CREATE USER [' + SP.name + '] FROM LOGIN [' + SP.name + '];' AS user_command FROM master.sys.[server_principals] SP WHERE SP.[type] = 'S' AND SP.name != 'sa' AND SP.name NOT LIKE ('#%') ORDER BY SP.[name]; --================================================================= -- AD GROUPS CREATE LOGINS AND DEFAULT DB USER COMMANDS --================================================================= SELECT SP.[principal_id] , SP.[name] , SP.[default_database_name] , 'CREATE LOGIN [' + SP.name + '] FROM WINDOWS WITH DEFAULT_DATABASE = [master];' AS login_command , 'USE [master]; CREATE USER [' + SP.name + '] FROM LOGIN [' + SP.name + '];' AS user_command FROM master.sys.[server_principals] SP WHERE SP.[type] = 'G' AND SP.name NOT LIKE 'NT %' ORDER BY SP.[name]; --================================================================= -- AD LOGINS CREATE LOGINS AND DEFAULT DB USER COMMANDS --================================================================= SELECT SP.[principal_id] , SP.[name] , SP.[default_database_name] , 'CREATE LOGIN [' + SP.name + '] FROM WINDOWS WITH DEFAULT_DATABASE = [master];' AS login_command , 'USE [master]; CREATE USER [' + SP.name + '] FROM LOGIN [' + SP.name + '] WITH DEFAULT_SCHEMA=[dbo];' AS user_command FROM master.sys.[server_principals] SP WHERE SP.[type] = 'U' AND SP.name NOT LIKE 'NT %' ORDER BY SP.[name]; --================================================================= -- SERVER ROLE MEMBERS --================================================================= SELECT R.name AS server_role , P.name AS role_member , 'EXEC master..sp_addsrvrolemember N' + '''' + P.name + '''' + ', N' + '''' + R.name + '''' + ';' AS command FROM sys.server_role_members RM INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_id INNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE') R ON RM.role_principal_id = R.principal_id WHERE P.name NOT LIKE '#%' AND P.name NOT LIKE 'NT %' AND P.type_desc <> 'SERVER_ROLE' AND P.name NOT IN ('sa') ORDER BY R.[name], P.[name];

Part One: Scripting the SQL Logins

The first command gives us the dynamically-created SQL commands for migrating our SQL logins.

After swapping out the template parameter corresponding to the default database setting (I use master as all I’m doing is giving it public role access and that role does not receive non-default permissions in my environments) I execute this section’s code. It provides me with two dynamic scripts to run: one for creating the login with it’s current SID and hashed password intact via sp_help_revlogin and the other for creating the default database’s corresponding user credential.

Sample output from that section would look like this:

Execute code contained in the script_command column on the current host to generate the CREATE LOGIN command to run on the new host:

Then you would run that CREATE LOGIN command on the new host as well as the contents of the user_command column. If you have multiple logins here simply copy and paste the script_command column and paste into a new query window pointed at the current host and execute it. Then copy and paste those generated commands output from the multiple sp_help_revlogin calls into a new query pointed at the new host and execute it. I find text output works best for this step. Then copy and paste all rows from the user_command column and run that against the new host. All SQL logins are ready for you at this point on the new host, their SIDs match, and their logins match between old and new hosts.

Part Two: Scripting the AD Group Security Objects

The second command gives us the dynamically-created SQL commands for migrating our logins based on Active Directory (AD) Groups.
This process is much more streamlined than dealing with SQL-based logins since there is no need to be concerned about matching SIDs and passwords. Active Directory takes care of all of that for you. All you need to do here is take the output generated from this code section and copy/paste the login_command column output into a new query window pointing at the new host and execute it. Then repeat with the output from the user_command column. A sample of the output you would expect to see is shown below:

The reason we treat AD Groups and AD Logins differently is because until SQL 2012 you could not assign a default schema to an AD Group. Therefore if you wish to run this against SQL 2012 you can simply ignore this code block and alter the next code block to include both AD Logins (SP.[type] = ‘U’) as well as AD Groups (SP.[type] = ‘G’) not a big deal.

Part Three: Scripting the AD Logins

The third command gives us the dynamically-created SQL commands for migrating our AD-based logins.
This is the same process as the AD Group command with the exception that the dynamic SQL code for the user creation inside the default database also includes a default schema. Run it in the same fashion as you did with the output from the AD Group section. Alter the WHERE clause as mentioned above if you’re on SQL 2012 and wish to set the default schema for AD Groups (recommended.)

Scripting Server Role Memberships

The final command gives us the dynamically-created SQL commands for migrating any SQL instance role memberships.
The final process is to script out any server role memberships that need to migrate as well. Hopefully there are not many other than those the DBA team has been granted. All those other people with role memberships – particularly sysadmin – may just get you fired one day:

The process is easy. All logins moved in a couple of minutes or less and everything matches. Then it’s just the matter of migrating your databases, which is another task all together.