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.sysloginsdocumentation 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_principalscontains the bulk of the information needed, andsys.sql_loginsone 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
