Login Script Generation For Migration or DR

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:

1. Creating Logins from Windows accounts
2. Creating logins that are authenticated by SQL Server
3. 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, and sys.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 from sys.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