sp_help_revlogin

Background

Restored a database and after running sp_change_users_login, found that there were sql logins that are were still misaligned.

Knew that I needed to copy the logins over, as well.

 

SSIS

There are some SSIS tasks that helps.

 

Legacy

But , why go new school when old school holds.

 

sp_help_revlogin

  1. MSFT
    • How to transfer logins and passwords between instances of SQL Server
      Link

Revision

Here is a revised code

Outline

  1. Added argument
    • @skipSystemAccounts
      • Ability to skip system accounts such as
        • Policy Accounts
          • ##MS_PolicyEventProcessingLogin##
          • ##MS_PolicyTsqlExecutionLogin##
        • NT AUTHORITY\SYSTEM
        • NT SERVICE%
    • Issue suser_id to check for account prior to creation

Code


/*

    How to transfer logins and passwords between instances of SQL Server
    https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

*/

USE [master]
GO

IF OBJECT_ID ('sp_hexadecimal') IS NULL
begin

  exec('create procedure [dbo].[sp_hexadecimal] as ')

end
GO

ALTER PROCEDURE [dbo].[sp_hexadecimal]
      @binvalue varbinary(256)
   ,  @hexvalue varchar (514) OUTPUT
AS
begin

    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    
    
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    
    WHILE (@i <= @length)
    BEGIN

      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

end

GO
                
 
IF OBJECT_ID ('sp_help_revlogin') IS NULL
begin

  exec('create PROCEDURE [dbo].[sp_help_revlogin] as ')

end
GO

ALTER PROCEDURE [dbo].[sp_help_revlogin]
(
       @loginName           sysname = NULL 
     , @skipSystemAccounts  bit = 0
)
AS
begin

    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
 
    DECLARE @STRING_EXISTENCE_CHECK_PREFIX  varchar(120)
    DECLARE @STRING_BEGIN                   varchar(120)
    DECLARE @STRING_END                     varchar(120)

    declare @CHAR_CRLF                      varchar(30)

    declare @tsql                           nvarchar(4000)

    set @STRING_EXISTENCE_CHECK_PREFIX = 'if suser_id(''%s'') is null '  
    set @STRING_BEGIN = ' begin '
    set @STRING_END = ' end '
     
    set @CHAR_CRLF  = char(13) + char(10);
     
    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 ( p.[name] = l.[name] ) 
    
        WHERE p.[type] IN ( 'S', 'G', 'U' ) 
    
        AND   p.[name] <> 'sa'
    
        AND   p.[name] = isNull(@loginName, p.[name])

        AND  (

                  (@skipSystemAccounts = 0)

                or
                (

                        ( @skipSystemAccounts = 1 )

                    --skip NT SERVICE
                    and (

                            ( 
                                p.[name] not in 
                                    ( 
                                          '##MS_PolicyEventProcessingLogin##'
                                        , '##MS_PolicyTsqlExecutionLogin##'
                                    )
                            )
                    
                            and
                            ( 
                                p.[name] not in 
                                    ( 
                                        'NT AUTHORITY\SYSTEM'
                                    )
                            )

                            and ( p.[name] not like 'NT SERVICE%')
                        )

                )

            )

        order by
                p.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

        set @tsql = replace( @STRING_EXISTENCE_CHECK_PREFIX, '%s', @name)
                        + @CHAR_CRLF
                        + @STRING_BEGIN
                        + @CHAR_CRLF
                        + @tmpstr
                        + @CHAR_CRLF
                        + @STRING_END
        
        PRINT @tsql
      
      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

end
GO

Source Control

github

DanielAdeniji/sp_help_revlogin
Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s