SQ Login_script

/*
    Based on Robert Davis' "Transferring Logins to a Database Mirror"

    Execute the script to generate scripts to transfer logins
*/

SET NOCOUNT ON;
USE master;
GO

DECLARE @LastId INT,
        @CurrentId INT,
        @SQLCmd NVARCHAR(MAX),
        @ServerPrincipal sysname,
        @IsDisabled INT,
        @Type CHAR(1),
        @SID VARBINARY(85),
        @SIDString NVARCHAR(100),
        @PasswordHash VARBINARY(256),
        @PasswordHashString NVARCHAR(300),
        @RoleName sysname,
        @Permissionstate NVARCHAR(60),
        @PermName sysname,
        @Class TINYINT,
        @MajorID INT;

DECLARE @Logins TABLE (
        LoginID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
        PrincipalId INT NOT NULL,
        [Name] sysname NOT NULL,
        [SID] VARBINARY(85) NOT NULL,
        IsDisabled INT NOT NULL,
        [Type] CHAR(1) NOT NULL,
        PasswordHash VARBINARY(256) NULL
    );

DECLARE @Roles TABLE (
        RoleID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
        RoleName sysname NOT NULL,
        LoginName sysname NOT NULL
    );

DECLARE @Permissions TABLE (
        PermID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
        LoginName sysname NOT NULL,
        PermState NVARCHAR(60) NOT NULL,
        PermName sysname NOT NULL,
        Class TINYINT NOT NULL,
        ClassDesc NVARCHAR(60) NOT NULL,
        MajorID INT NOT NULL,
        SubLoginName sysname NULL,
        SubEndPointName sysname NULL
    );

/*
    Gather information on the logins, the roles they are members of and any
    permissions they've been explicitly granted or denied...
*/

-- Grab logins
/*
    As written the query below excludes sa, logins starting with ##, NT Authority
    and NT Service accounts and distributor_admin.

    You can adjust the WHERE clause as desired to script out specific logins:

        WHERE p.name IN ( N'B', N'Tom', N'Judy' );

    or perhaps just SQL Authenticated logins (U and G are Windows Authenticated
    accounts):

        WHERE p.type = 'S';
*/
INSERT INTO @Logins ( PrincipalId, Name, SID, IsDisabled, Type, PasswordHash )
SELECT p.principal_id, p.name, p.sid, p.is_disabled, p.type, l.password_hash
FROM    sys.server_principals p
        LEFT JOIN sys.sql_logins l ON l.principal_id = p.principal_id
WHERE   p.type IN ( 'U', 'G', 'S' ) AND
        p.name <> N'sa' AND
        p.name NOT LIKE N'##%' AND
        p.name NOT LIKE N'distributor_admin' AND
        p.name NOT LIKE N'NT [SA]%';

-- Their server role assignments...
INSERT INTO @Roles ( RoleName, LoginName )
SELECT r.name, p.name
FROM    sys.server_role_members rm
        INNER JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id
        INNER JOIN @Logins p ON p.PrincipalId = rm.member_principal_id
WHERE   r.type = 'R';

-- And any explicitly granted permissions
INSERT INTO @Permissions ( LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName )
SELECT p.name, sp.state_desc, sp.permission_name, sp.class, sp.class_desc,
        sp.major_id, subp.name, subep.name
FROM    @Logins p
        INNER JOIN sys.server_permissions sp ON sp.grantee_principal_id = p.PrincipalId
        LEFT JOIN sys.server_principals subp ON subp.principal_id = sp.major_id AND sp.class = 101
        LEFT JOIN sys.endpoints subep ON subep.endpoint_id = sp.major_id AND sp.class = 105;

/*
    Now generate statements to create each login, role membership and permission
    grant/deny...
*/

-- Script a Create Login statement for each entry in @Logins
SELECT @CurrentId = 1, @LastId = MAX(LoginID)
FROM    @Logins;

WHILE (@CurrentId <= @LastId)
BEGIN
    SELECT @ServerPrincipal = Name, @IsDisabled = IsDisabled,
            @Type = [Type], @SID = [SID], @PasswordHash = PasswordHash
    FROM    @Logins
    WHERE   LoginID = @CurrentId;

    BEGIN
        SET @SQLCmd = 'Create Login ' + QUOTENAME(@ServerPrincipal)

        -- For individual and group domain accounts...
        IF @Type IN ( 'U', 'G' )
        BEGIN
            SET @SQLCmd = @SQLCmd + ' From Windows;'
        END
        -- For SQL authenticated logins
        ELSE
        BEGIN
            SET @PasswordHashString = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)');
             SET @SQLCmd = @SQLCmd + ' With Password = ' + @PasswordHashString + ' HASHED, ';
            SET @SIDString = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)');
            SET @SQLCmd = @SQLCmd + 'SID = ' + @SIDString + ';';
        END

        PRINT @SQLCmd;

        -- Are any of the accounts currently disabled?
        IF @IsDisabled = 1
        BEGIN
            SET @SQLCmd = 'Alter Login ' + QUOTENAME(@ServerPrincipal) + ' Disable;'
            PRINT @SQLCmd;
        END
    END
    SET @CurrentId += 1;
END

-- Script an sp_addsrvrolemember statement for each entry in @Roles
SELECT @CurrentId = 1, @LastId = MAX(RoleID)
FROM    @Roles;

WHILE (@CurrentId <= @LastId)
BEGIN
    SELECT @ServerPrincipal = LoginName, @RoleName = RoleName
    FROM    @Roles
    WHERE   RoleID = @CurrentId;

    PRINT 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''', @loginame = ''' + @ServerPrincipal + ''';';

    SET @CurrentId += 1;
END

-- Script out any specific Grant or Deny statements
SELECT @CurrentId = 1, @LastId = MAX(PermID)
FROM    @Permissions;

WHILE (@CurrentId <= @LastId)
BEGIN
    SELECT @Permissionstate = PermState, @PermName = PermName,
            @Class = Class, @ServerPrincipal = LoginName, @MajorID = MajorID,
            @SQLCmd = PermState + SPACE(1) + PermName + SPACE(1) +
            CASE Class
                WHEN 101 THEN 'On Login::' + QUOTENAME(SubLoginName)
                WHEN 105 THEN 'On ' + ClassDesc + '::' + QUOTENAME(SubEndPointName)
                ELSE ''
            END + ' To ' + QUOTENAME(LoginName) + ';'
    FROM    @Permissions
    WHERE   PermID = @CurrentId;

    PRINT @SQLCmd;

    SET @CurrentId += 1;
END

MicroSoft SQL Server

Pages

SQ Login_script_2015013