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:
- SQL Logins
- Trusted Authentication (aka Active Directory or AD) Logins
- Trusted Authentication (AD) Groups
- Default Database Users
- Server Role Memberships
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]
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @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 + 1
END
SELECT @hexvalue = @charvalue;
GO
--=============================
-- CREATE sp_help_revlogin
--=============================
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @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 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 = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
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
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
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: austenford
CREATE 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: chriscornell
CREATE 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: davidbowie
CREATE 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: prince
CREATE 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: timford
CREATE 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: trevorford
CREATE 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 [SQLCRUISE\timf] FROM WINDOWS WITH DEFAULT_DATABASE = [master];
CREATE LOGIN [SQLCRUISE\app_service] FROM WINDOWS WITH DEFAULT_DATABASE = [master];
CREATE LOGIN [SQLCRUISE\comms] FROM WINDOWS WITH DEFAULT_DATABASE = [master];
USE [master]; CREATE USER [SQLCRUISE\timf] FROM LOGIN [SQLCRUISE\timf] WITH DEFAULT_SCHEMA=[dbo];
USE [master]; CREATE USER [SQLCRUISE\app_service] FROM LOGIN [SQLCRUISE\app_service] WITH DEFAULT_SCHEMA=[dbo];
USE [master]; CREATE USER [SQLCRUISE\comms] FROM LOGIN [SQLCRUISE\comms] 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 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];
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'SQLCRUISE\timf', 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 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];
