Cloning Security Objects Between Servers

It’s a common task for a Database Administrator to have to replicate logins across multiple SQL Server instances. Whether you’re setting up a separate environment to mimic production (Dev, UAT, etc.) or perhaps staging a replacement server or multiple nodes that will comprise an Availability Group you likely will need security objects (logins, role membership, users) set up identically. There are a plethora of issues that come into play that don’t make this as clearly defined as you would expect. SID differences between instance for SQL Server logins, mismatched passwords, and orphaned users are but a few of these issues that I’ve written about previously.

I have a rule that if I’m going to perform a function multiple times I’m going to take the time to build a script that I can re-use to make my life easier and free me up to either work more efficiently in other areas or work less and accomplish the same amount of results expected of me. That is why many years ago I came up with the script I’m sharing with you today that allows you to create all of the following security objects on one source instance of SQL to be distributed to other instances as needed:

The point is to ensure that all SIDs match, all server-level role memberships are assigned, and there is no risks of a default database assignment not having a corresponding user object which would cause connectivity issues potentially.

Prerequisites

There are two widely distributed scripts in the SQL Server community that have easily been around for more than a decade that are necessary for the script I’ve created and am sharing to run properly: sp_help_revlogin and sp_hexadecimal. sp_help_revlogin produces code that will recreate a login and force the SID as part of the creation to reduce the likelihood of SID mismatches between logins and users copied between instances. sp_hexadecimal is required by sp_help_revlogin to translate the password hash that is created into text form to be used in the script.  Both stored procedures’ code is listed below. Please execute before proceeding:

--=============================-- CREATE sp_hexadecimal--=============================USE [master]GOCREATE PROCEDURE sp_hexadecimal    @binvalue varbinary(256),    @hexvalue varchar (514) OUTPUTASDECLARE @charvalue varchar (514)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @length = DATALENGTH (@binvalue)SELECT @hexstring = '0123456789ABCDEF'WHILE (@i <= @length)BEGIN  DECLARE @tempint int  DECLARE @firstint int  DECLARE @secondint int  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))  SELECT @firstint = FLOOR(@tempint/16)  SELECT @secondint = @tempint - (@firstint*16)  SELECT @charvalue = @charvalue +    SUBSTRING(@hexstring, @firstint+1, 1) +    SUBSTRING(@hexstring, @secondint+1, 1)  SELECT @i = @i + 1ENDSELECT @hexvalue = @charvalue;GO--=============================-- CREATE sp_help_revlogin--=============================USE [master]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @type varchar (1)DECLARE @hasaccess intDECLARE @denylogin intDECLARE @is_disabled intDECLARE @PWD_varbinary  varbinary (256)DECLARE @PWD_string  varchar (514)DECLARE @SID_varbinary varbinary (85)DECLARE @SID_string varchar (514)DECLARE @tmpstr  varchar (1024)DECLARE @is_policy_checked varchar (3)DECLARE @is_expiration_checked varchar (3)DECLARE @defaultdb sysname IF (@login_name IS NULL)  DECLARE login_curs CURSOR FOR  SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l  ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'ELSE  DECLARE login_curs CURSOR FORSELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l  ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_nameOPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denyloginIF (@@fetch_status = -1)BEGIN  PRINT 'No login(s) found.'  CLOSE login_curs  DEALLOCATE login_curs  RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script 'PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''WHILE (@@fetch_status <> -1)BEGIN  IF (@@fetch_status <> -2)  BEGIN    PRINT ''    SET @tmpstr = '-- Login: ' + @name    PRINT @tmpstr    IF (@type IN ( 'G', 'U'))    BEGIN -- NT authenticated account/group  SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'    END    ELSE BEGIN -- SQL Server authentication    -- obtain password and sid    SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT  -- obtain password policy state    SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name    SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name  SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'IF ( @is_policy_checked IS NOT NULL )    BEGIN  SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked    END    IF ( @is_expiration_checked IS NOT NULL )    BEGIN  SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked    END    END    IF (@denylogin = 1)    BEGIN -- login is denied access  SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )    END    ELSE IF (@hasaccess = 0)    BEGIN -- login exists but does not have access  SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )    END    IF (@is_disabled = 1)    BEGIN -- login is disabled  SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'    END    PRINT @tmpstr  END  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin   ENDCLOSE login_cursDEALLOCATE login_cursRETURN 0GO

Login Clone Script

Now that you’ve the pre-requisites it’s time to discuss the clone script that brings you here today. The script is broken down into separate sections that match the security objects itemized earlier in the article. For the sake of readability of the article I’m splitting up each section to look at the code, the results, and what you need to do with the results that are generated in order to apply them to the “clone” target. While I'll be breaking down the script by section, the full script will be provided at the end of the article for your convenience.

Section 1: SQL Logins

In this section I utilize the two prerequisite stored procedures to generate five columns – three are for identification only and the final two (script_command and user_command) are what you need to be concerned about most.  script_command is generated through calls to sp_help_revlogin to create code that when executed provides the CREATE LOGIN commands to be executed against your clone target. User_command is the dynamically generated code that will create the user object in the master database (by default but completely alterable in the code should you choose.)

--=================================================================-- 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];

As you’ll see two columns of code are generated. The script_command results can be copied as a column and executed all at once against the source server you’ve run this section against to begin with. I always use a second query window to do so because you want to continue to reference the results from this initial query.

Based upon the example above you’d run the following query against the SOURCE instance again:

EXEC [sp_help_revlogin] 'austenford';EXEC [sp_help_revlogin] 'chriscornell';EXEC [sp_help_revlogin] 'davidbowie';EXEC [sp_help_revlogin] 'prince';EXEC [sp_help_revlogin] 'timford';EXEC [sp_help_revlogin] 'trevorford';

That would result in the following output:

/* sp_help_revlogin script** Generated Jun 29 2017 10:46PM on TIMF-X1-SEA */-- Login: austenfordCREATE LOGIN [austenford] WITH PASSWORD = 0x0200FEE6F12D296A9E96D0CAB6B3AF6B0A83F8A6B3B57A403356CFE1F888B8E7D2E20FD7E4E5FA99742326453A1F291988BBB278443391EEA085EEEED5C7678BE2C5756C7DBF HASHED, SID = 0x4AD4F9838102434D846EFE5CC75B9D01, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF/* sp_help_revlogin script** Generated Jun 29 2017 10:46PM on TIMF-X1-SEA */-- Login: chriscornellCREATE LOGIN [chriscornell] WITH PASSWORD = 0x0200559FC679B189BE312D416AD5A2EC60D1F628D9A696291B0BAD3C9633A7CD295D7F29981481FE2490E4EAECEB414E74EC4BEEB0CDD1E0666C7596167C85C75AACD11888D0 HASHED, SID = 0x4FF2C5D0B93ED145B9F96FC7BA265884, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF/* sp_help_revlogin script** Generated Jun 29 2017 10:46PM on TIMF-X1-SEA */-- Login: davidbowieCREATE LOGIN [davidbowie] WITH PASSWORD = 0x0200BB0728E043983CA0A9E5F1D459EBE43CF3BB1D226FCBD8C71A12E63C3EF3579A97FC238FA23AA8B8923D64898C0F521A2D050C4984F7DF1CA516844127DF7808E2BB34CD HASHED, SID = 0x3231CA55367D734DB3BD259C27AE1A15, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF/* sp_help_revlogin script** Generated Jun 29 2017 10:46PM on TIMF-X1-SEA */-- Login: princeCREATE LOGIN [prince] WITH PASSWORD = 0x02001850FE91CDEFC1180A2B512556405AF866B2E1E21E4224E3A885F07694B8AC61A0F99F32DC4259CFD8B3EFFCB4CF1456C94919D2BBADFE16C9B370368AA236D6E28C643A HASHED, SID = 0x76AB2554249C29428504129EEE13C6F7, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF/* sp_help_revlogin script** Generated Jun 29 2017 10:46PM on TIMF-X1-SEA */-- Login: timfordCREATE LOGIN [timford] WITH PASSWORD = 0x020090D83AF25B9841B1AD32C4EE2729C000AA8BE5B6399FBDCADA9D3E20FCFB0F4CE276C8200D0E7035B5237CBD7054FB37ACBD90C7399D9EE0BF9AEA71ED54A85AB113C931 HASHED, SID = 0x75D053277EF82D419D20E92D21C2801C, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF/* sp_help_revlogin script** Generated Jun 29 2017 10:46PM on TIMF-X1-SEA */-- Login: trevorfordCREATE LOGIN [trevorford] WITH PASSWORD = 0x02003F9AF29FF033CA2C57044B1922A9909BFF5317E976A109B6DFE3662912BE72B303C718FAC51C7049379490E95A70A113556D991E78BFE920F267196484D8A239492EFE2B HASHED, SID = 0x95C4115AAC64C14EAEE73A7CC6012E4B, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Each SQL Login has a code block providing the necessary parameters to replicate the SOURCE instance login to the TARGET instance: a hashed password, a forced SID, and then assignment to the master database by default and password policy and expiration checks set to OFF. You can use a simple Control+H to do a find and replace to change those values in the script should you choose to do so.

At this point I create the query window connected to the TARGET instance and paste the code above followed by the code from the user_command column:

USE [master]; CREATE USER [austenford] FROM LOGIN [austenford];USE [master]; CREATE USER [chriscornell] FROM LOGIN [chriscornell];USE [master]; CREATE USER [davidbowie] FROM LOGIN [davidbowie];USE [master]; CREATE USER [prince] FROM LOGIN [prince];USE [master]; CREATE USER [timford] FROM LOGIN [timford];USE [master]; CREATE USER [trevorford] FROM LOGIN [trevorford];

When executed against the TARGET you now have cloned SQL Authentication logins from SOURCE to TARGET with matching SIDs.

This is the only section that requires you to have a secondary window open for the SOURCE instance. Close the query window you opened to run the generated sp_help_revlogin commands and you can move on to section 2.

Section 2: AD Logins

Active Directory logins are much easier to create because the burden of SID mis-matches doesn’t exist. This section’s code is essentially dynamic SQL to tap into the AD logins in master.sys.server_principals to build a CREATE LOGIN… FROM WINDOWS script for any AD login filtered to avoid local AD logins that are machine-dependent.

--=================================================================-- 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];

The associated script_command and user_command columns look like this once pasted into the query connected to my TARGET instance:

CREATE LOGIN [SQLCRUISEtimf] FROM WINDOWS WITH DEFAULT_DATABASE = [master];CREATE LOGIN [SQLCRUISEapp_service] FROM WINDOWS WITH DEFAULT_DATABASE = [master];CREATE LOGIN [SQLCRUISEcomms] FROM WINDOWS WITH DEFAULT_DATABASE = [master];USE [master]; CREATE USER [SQLCRUISEtimf] FROM LOGIN [SQLCRUISEtimf] WITH DEFAULT_SCHEMA=[dbo];USE [master]; CREATE USER [SQLCRUISEapp_service] FROM LOGIN [SQLCRUISEapp_service] WITH DEFAULT_SCHEMA=[dbo];USE [master]; CREATE USER [SQLCRUISEcomms] FROM LOGIN [SQLCRUISEcomms] WITH DEFAULT_SCHEMA=[dbo];

Section 3: AD Groups

AD groups are handled in a very similar fashion as AD logins. The only distinction between both categories is the value of the type column in master.sys.server_principles (‘U’ for AD logins, ‘G’ for AD groups.)  I used to split out AD Groups from AD Logins because of the behavior of SQL Server 2005 not allowing you to set a default schema for an AD Group. Since SQL Server 2005 is no longer supported there is no need to do this. You could easily modify this script in your environment so as to include both (type = 'G' or type = 'U').

--=================================================================-- 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 + '] WITH DEFAULT_SCHEMA=[dbo];' AS user_command FROM master.sys.[server_principals] SP WHERE SP.[type] = 'G'AND SP.name NOT LIKE 'NT %'ORDER BY SP.[name];

Section 4: Server Role Membership

The final section covers server role membership. When migrating databases between instances for such things as seeding test, training, or UAT environments none of the server level role rights move with those databases. This section of the script allows for ensuring those server level permissions persist to the TARGET (clone) instance.

--=================================================================-- 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 commandFROM sys.server_role_members RM INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_idINNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE') RON RM.role_principal_id = R.principal_idWHERE 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];

Unlike the other sections this code generates only the singly-needed ad-hoc SQL text to call the sp_addrolemember command. The results would look something like this:

EXEC master..sp_addsrvrolemember N'SQLCRUISEtimf', N'sysadmin';

Simply run that code on the TARGET instance and you’re all set.

Conclusion

Using this script allows for you to move security objects between instances without much effort. That being said for those who are users and advocates for Powershell I’ll be exploring just how much the world has changed as we examine how Powershell makes this even easier in an article coming next month.

As Promised: here is the full code...

--=================================================================-- 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 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];--=================================================================-- 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 + '] WITH DEFAULT_SCHEMA=[dbo];' AS user_command FROM master.sys.[server_principals] SP WHERE SP.[type] = 'G'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 commandFROM sys.server_role_members RM INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_idINNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE') RON RM.role_principal_id = R.principal_idWHERE 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];