I needed a script to create logins on a second server to match the primary. There are reasons why using, say, the powershell dba tool set isn’t an option (eg the server isn’t built, or the two can’t communicate, or your security team doesn’t allow for the use of this sort of thing), so let’s have a look at what we can do just in T-SQL.
There are three things to look at:
- Creating Logins from Windows accounts
- Creating logins that are authenticated by SQL Server
- Assigning membership of the appropriate server roles
Note that we’re just creating a snapshot of what’s there – we’re not aiming to keep these things in true synchronisation between servers. Note also that this script will only create accounts that don’t exist. If there’s an account already there with the same name, the script generated will not recreate it with new options.
sys.syslogins, sys.server_principals, sys.sql_logins
There are a couple of ways of getting at login information:
- sys.syslogins
- This is the old way. Try not to use it any more. The
sys.syslogins
documentation says it will be removed in a future version of Microsoft SQL Server. - sys.server_principals and sys.sql_logins
- These are the new(ish) DMVs to use –
sys.server_principals
contains the bulk of the information needed, andsys.sql_logins
one contains a few extra bits that are specific only to SQL Server authentication (passwords, policy flags, etc), but the bulk is inherited fromsys.server_principals
Scripting Windows Login Accounts
This is probably the easiest script to write, as it just references sys.server_principals
. I’ve written a simple “IF NOT EXISTS” wrapper around the whole thing, to make sure we don’t try to create an account that’s already there.
SELECT N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(name, '''') + N') BEGIN CREATE LOGIN ' + QUOTENAME(name) + N' FROM WINDOWS WITH DEFAULT_LANGUAGE = ' + default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) + N' END;' AS SQLQuery FROM sys.server_principals WHERE type IN ('U', 'G') AND name NOT LIKE 'NT %';
Note that we’re excluding the NT service accounts / default accounts, as there’s no point in generating those, as they’ll already be there on the target anyway.
Scripting SQL Authenticated Accounts
Really? We still have these? Unfortunately, yes – and I can’t see them going away any time soon, unfortunately. So, here we are.
As above, I’m generating the “IF NOT EXISTS” wrapper. This time, we’re querying sys.sql_logins, as it contains only those server principals, and the extra (password / policy) information required by these accounts.
SELECT N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(sl.name, '''') + N') BEGIN CREATE LOGIN ' + QUOTENAME(sl.name) + N' WITH PASSWORD = ' + CAST(CONVERT(VARCHAR(256), CAST(LOGINPROPERTY(sl.name, 'PasswordHash') AS VARBINARY(256)), 1) AS NVARCHAR(MAX)) + ' HASHED' + N', SID=' + CONVERT(VARCHAR(256), sl.sid, 1) + N', DEFAULT_LANGUAGE = ' + sl.default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(sl.default_database_name) + N', CHECK_EXPIRATION = ' + CASE sl.is_expiration_checked WHEN 1 THEN N'ON' ELSE N'OFF' END + N', CHECK_POLICY = ' + CASE sl.is_policy_checked WHEN 1 THEN N'ON' ELSE N'OFF' END + N'; END;' AS SQLQuery FROM sys.sql_logins sl WHERE sl.name NOT LIKE '##%' AND sl.name NOT LIKE 'sa';
Server Role Memberships
This is the final step – assigning role memberships. For this, we need to use the sys.server_role_members
DMV as well as sys.server_principals (twice, once for the role name, once for the member name). That’s the big CTE before the main SELECT statement.
WITH RoleMembers AS ( SELECT srm.role_principal_id, srm.member_principal_id, r.name AS RoleName, m.name AS MemberName FROM sys.server_role_members srm INNER JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id INNER JOIN sys.server_principals m ON srm.member_principal_id = m.principal_id ) SELECT N'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(MemberName, '''') + N') BEGIN ALTER SERVER ROLE ' + RoleName + N' ADD MEMBER ' + +QUOTENAME(MemberName) + N'; END;' AS SQLQuery FROM RoleMembers WHERE RoleMembers.member_principal_id IN ( SELECT principal_id FROM sys.server_principals WHERE type IN ('U', 'S') AND name NOT LIKE 'NT %' AND name NOT LIKE 'sa' AND name NOT LIKE '##%' );
Wrapping this up
I’ve put all this functionality into a single view that can be queried to return the statements in the order above – logins before roles – so that it can be easily used to generate everything you need. The View definition script is below. Note the extra field (“Ord”) which we won’t query later, but will use to sort the results.
USE [master]; GO /****** Object: View [dbo].[ExportLoginQueries] Script Date: 24/03/2020 16:00:58 ******/ SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO IF OBJECT_ID('dbo.ExportLoginQueries', 'V') IS NULL BEGIN EXEC ('CREATE VIEW dbo.ExportLoginQueries AS SELECT 1 AS [one]'); END; GO ALTER VIEW [dbo].[ExportLoginQueries] AS WITH RoleMembers AS ( SELECT srm.*, r.name AS RoleName, m.name AS MemberName FROM sys.server_role_members srm INNER JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id INNER JOIN sys.server_principals m ON srm.member_principal_id = m.principal_id ) SELECT 3 AS Ord, N'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(MemberName, '''') + N') BEGIN ALTER SERVER ROLE ' + RoleName + N' ADD MEMBER ' + +QUOTENAME(MemberName) + N'; END;' AS SQLQuery FROM RoleMembers WHERE RoleMembers.member_principal_id IN ( SELECT principal_id FROM sys.server_principals WHERE type IN ('U', 'S') AND name NOT LIKE 'NT %' AND name NOT LIKE 'sa' AND name NOT LIKE '##%' ) UNION ALL SELECT 1 AS Ord, N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(name, '''') + N') BEGIN CREATE LOGIN ' + QUOTENAME(name) + N' FROM WINDOWS WITH DEFAULT_LANGUAGE = ' + default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) + N' END;' AS SQLQuery FROM sys.server_principals WHERE type = 'U' AND name NOT LIKE 'NT %' UNION ALL SELECT 2 AS Ord, N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(sl.name, '''') + N') BEGIN CREATE LOGIN ' + QUOTENAME(sl.name) + N' WITH PASSWORD = ' + CAST(CONVERT(VARCHAR(256), CAST(LOGINPROPERTY(sl.name, 'PasswordHash') AS VARBINARY(256)), 1) AS NVARCHAR(MAX)) + ' HASHED' + N', SID=' + CONVERT(VARCHAR(256), sl.sid, 1) + N', DEFAULT_LANGUAGE = ' + sl.default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(sl.default_database_name) + N', CHECK_EXPIRATION = ' + CASE sl.is_expiration_checked WHEN 1 THEN N'ON' ELSE N'OFF' END + N', CHECK_POLICY = ' + CASE sl.is_policy_checked WHEN 1 THEN N'ON' ELSE N'OFF' END + N'; END;' AS SQLQuery FROM sys.sql_logins sl WHERE sl.name NOT LIKE '##%' AND sl.name <> 'sa'; GO
All that needs to be done now is to write something to extract this as & when required. For example, using bcp
:
bcp "SELECT SQLQuery FROM dbo.ExportLoginQueries ORDER BY Ord, SQLQuery" queryout "<<PathToBackupDirectory>>\ExportedSQLLogins.sql" -t -c -S <<servername>> -T
Pingback: Using T-SQL to Generate a Login Migration Script – Curated SQL
Pingback: T-SQL Script to Fix Orphaned DB Users Easily – Eitan Blumin's Blog
This is a great solution, thank you for sharing!
I have a relevant solution in my own blog for finding and fixing orphaned database users, so this could be very handy together:
I think there’s just one thing missing in your scripts, and that’s the generation of server-level securables (besides server roles).
But awesome solution nonetheless!
Ooh, good call. I’ll have to think about the server securables tomorrow.
Pingback: T-SQL Script to Fix Orphaned DB Users Easily - Eitan Blumin's blog