Wednesday, July 3, 2013

Dynamics GP - SQL Capture Logins Script

Original Post
https://community.dynamics.com/gp/f/32/p/61646/199961.aspx#.UdQ102LHIjg


/************************************************************************************************


*


* Purpose: To capture all the SQL Server logins with the binary password regardless of SQL version.


* Script is compatible with SQL 7 / SQL 2000 / SQL 2005. Need to save the results


* to a text file and run within a new query window.


*


* Created Date: Initial - 01/13/2006


*


* Revisions: 01/15/2006 - Made some formatting changes to the output text.


* 01/16/2006 - Made syntax change to account for Binary version of SQL Server.


* 01/16/2006 - Change version SQL version check because of syntax differences between


* SQL 2000 and 2005 with @@version.


* 04/10/2007 - Made change to deal with user names that begin with numbers instead of characters.


* 11/14/2008 - Made syntax change to update for SQL 2008


*


************************************************************************************************/

USE master 

go 

IF Object_id ('sp_hexadecimal') IS NOT NULL 
  DROP PROCEDURE sp_hexadecimal 

go 

CREATE PROCEDURE Sp_hexadecimal @binvalue VARBINARY(256), 
                                @hexvalue VARCHAR(256) output 
AS 
    DECLARE @charvalue VARCHAR(256) 
    DECLARE @i INT 
    DECLARE @length INT 
    DECLARE @hexstring CHAR(16) 

    SELECT @charvalue = '0x' 

    SELECT @i = 1 

    SELECT @length = Datalength (@binvalue) 

    SELECT @hexstring = '0123456789ABCDEF' 

    WHILE ( @i <= @length ) 
      BEGIN 
          DECLARE @tempint INT 
          DECLARE @firstint INT 
          DECLARE @secondint INT 

          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 

go 

IF Object_id ('sp_help_revlogin') IS NOT NULL 
  DROP PROCEDURE sp_help_revlogin 

go 

CREATE PROCEDURE Sp_help_revlogin @login_name SYSNAME = NULL 
AS 
    DECLARE @name SYSNAME 
    DECLARE @xstatus INT 
    DECLARE @binpwd VARBINARY (256) 
    DECLARE @txtpwd SYSNAME 
    DECLARE @tmpstr VARCHAR (256) 
    DECLARE @SID_varbinary VARBINARY(85) 
    DECLARE @SID_string VARCHAR(256) 

    IF ( @login_name IS NULL ) 
      DECLARE login_curs CURSOR FOR 
        SELECT sid, 
               name, 
               xstatus, 
               password 
        FROM   master..sysxlogins 
        WHERE  srvid IS NULL 
               AND name <> 'sa' 
    ELSE 
      DECLARE login_curs CURSOR FOR 
        SELECT sid, 
               name, 
               xstatus, 
               password 
        FROM   master..sysxlogins 
        WHERE  srvid IS NULL 
               AND name = @login_name 

    OPEN login_curs 

    FETCH next FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd 

    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 '' 

    PRINT 'DECLARE @pwd sysname' 

    WHILE ( @@fetch_status <> -1 ) 
      BEGIN 
          IF ( @@fetch_status <> -2 ) 
            BEGIN 
                PRINT '' 

                SET @tmpstr = '-- Login: ' + @name 

                PRINT @tmpstr 

                IF ( @xstatus & 4 ) = 4 
                  BEGIN -- NT authenticated account/group 
                      IF ( @xstatus & 1 ) = 1 
                        BEGIN -- NT login is denied access 
                            SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name 
                                          + 
                                          '''' 

                            PRINT @tmpstr 
                        END 
                      ELSE 
                        BEGIN -- NT login has access 
                            SET @tmpstr = 'EXEC master..sp_grantlogin ''' + 
                                          @name + 
                                          '''' 

                            PRINT @tmpstr 
                        END 
                  END 
                ELSE 
                  BEGIN -- SQL Server authentication 
                      IF ( @binpwd IS NOT NULL ) 
                        BEGIN -- Non-null password 
                            EXEC Sp_hexadecimal 
                              @binpwd, 
                              @txtpwd out 

                            IF ( @xstatus & 2048 ) = 2048 
                              SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' 
                                            + @txtpwd + ')' 
                            ELSE 
                              SET @tmpstr = 
                              'SET @pwd = CONVERT (varbinary(256), ' 
                              + @txtpwd + ')' 

                            PRINT @tmpstr 

                            EXEC Sp_hexadecimal 
                              @SID_varbinary, 
                              @SID_string out 

                            SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
                                          + '' 
                                          + ''', @pwd, @sid = ' + @SID_string 
                                          + ', @encryptopt = ' 
                        END 
                      ELSE 
                        BEGIN 
                            -- Null password 
                            EXEC Sp_hexadecimal 
                              @SID_varbinary, 
                              @SID_string out 

                            SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
                                          + '' 
                                          + ''', NULL, @sid = ' + @SID_string 
                                          + ', @encryptopt = ' 
                        END 

                      IF ( @xstatus & 2048 ) = 2048 
                        -- login upgraded from 6.5 
                        SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
                      ELSE 
                        SET @tmpstr = @tmpstr + '''skip_encryption''' 

                      PRINT @tmpstr 
                  END 
            END 

          FETCH next FROM login_curs INTO @SID_varbinary, @name, @xstatus, 
          @binpwd 
      END 

    CLOSE login_curs 

    DEALLOCATE login_curs 

    RETURN 0 

go 

IF Object_id ('seeMigrateSQLLogins') IS NOT NULL 
  DROP PROCEDURE seemigratesqllogins 

go 

CREATE PROCEDURE Seemigratesqllogins @login_name SYSNAME = NULL 
AS 
    DECLARE @name          CHAR(50), 
            @binpwd        VARBINARY (256), 
            @txtpwd        SYSNAME, 
            @tmpstr        VARCHAR (256), 
            @SID_varbinary VARBINARY(85), 
            @SID_string    VARCHAR(256), 
            @Is_Policy     BIT, 
            @Is_Exp        BIT, 
            @type          CHAR(1), 
            @Pol           CHAR(3), 
            @Exp           CHAR(3) 

    SET nocount ON 

    CREATE TABLE #logins 
      ( 
         [name]                  NVARCHAR(128) NOT NULL, 
         [sid]                   VARBINARY(85) NOT NULL, 
         [type]                  CHAR(1) NOT NULL, 
         [is_policy_checked]     BIT DEFAULT 0, 
         [is_expiration_checked] BIT DEFAULT 0, 
         [password_hash]         VARBINARY(256) 
      ) 

    INSERT #logins 
           (name, 
            sid, 
            type) 
    SELECT name, 
           sid, 
           type 
    FROM   sys.server_principals 
    WHERE  ( type_desc = 'SQL_LOGIN' 
              OR type_desc = 'WINDOWS_LOGIN' ) 
           AND name <> 'sa' 
           AND name <> 'NT AUTHORITY\SYSTEM' 

    UPDATE a 
    SET    a.is_policy_checked = b.is_policy_checked, 
           a.is_expiration_checked = b.is_expiration_checked, 
           a.password_hash = b.password_hash 
    FROM   #logins a, 
           sys.sql_logins b 
    WHERE  a.sid = b.sid 

    SET nocount OFF 

    IF ( @login_name IS NULL ) --Not a single user, get the list 
      DECLARE seelogin_curs CURSOR FOR 
        SELECT name, 
               sid, 
               password_hash, 
               type, 
               is_policy_checked, 
               is_expiration_checked 
        FROM   #logins 
        WHERE  name <> 'sa' 
    ELSE 
      DECLARE seelogin_curs CURSOR FOR 
        SELECT name, 
               sid, 
               password_hash, 
               type, 
               is_policy_checked, 
               is_expiration_checked 
        FROM   #logins 
        WHERE  name = @login_name 

    OPEN seelogin_curs 

    FETCH next FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, 
    @Is_Policy, @Is_Exp 

    IF ( @@fetch_status = -1 ) 
      BEGIN 
          PRINT 'No login(s) found.' 

          CLOSE seelogin_curs 

          DEALLOCATE seelogin_curs 
      END 

    SET @tmpstr = '/* seeMigrateSQLLogins - For SQL Server 2005 Only ' 

    PRINT @tmpstr 

    SET @tmpstr = '** Generated ' 
                  + CONVERT (VARCHAR, Getdate()) + ' on ' 
                  + @@SERVERNAME + ' */' 

    PRINT @tmpstr 

    PRINT '' 

    WHILE ( @@fetch_status <> -1 ) 
      BEGIN 
          IF @type = 'S' 
            BEGIN 
                PRINT '/* SQL Login ******************/' 

                EXEC Sp_hexadecimal 
                  @binpwd, 
                  @txtpwd out 

                EXEC Sp_hexadecimal 
                  @SID_varbinary, 
                  @SID_string out 

                IF @Is_Policy = 1 
                  BEGIN 
                      SET @Pol = 'ON' 
                  END 
                ELSE 
                  BEGIN 
                      SET @Pol = 'OFF' 
                  END 

                IF @Is_Exp = 1 
                  BEGIN 
                      SET @Exp = 'ON' 
                  END 
                ELSE 
                  BEGIN 
                      SET @Exp = 'OFF' 
                  END 

                SET @tmpstr = 'Create Login [' + Rtrim(@name) 
                              + '] WITH PASSWORD = ' + @txtpwd 
                              + ' hashed, sid = ' + @SID_string 
                              + ', CHECK_POLICY = ' + @Pol 
                              + ', CHECK_EXPIRATION = ' + @Exp 

                PRINT @tmpstr 

                PRINT '' 
            END 
          ELSE 
            BEGIN 
                PRINT '/* SQL Login ******************/' 

                EXEC Sp_hexadecimal 
                  @SID_varbinary, 
                  @SID_string out 

                SET @tmpstr = 'Create Login [' + Rtrim(@name) 
                              + '] FROM WINDOWS; ' 

                PRINT @tmpstr 

                PRINT '' 
            END 

          FETCH next FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, 
          @type, 
          @Is_Policy, @Is_Exp 
      END 

    CLOSE seelogin_curs 

    DEALLOCATE seelogin_curs 

    DROP TABLE #logins 

go 

DECLARE @version2005 CHAR(5) 
DECLARE @version2008 CHAR(5) 

--Get the current version of SQL Server running 
SELECT @version2005 = Substring(@@version, 29, 4) 

SELECT @version2008 = Substring(@@version, 35, 4) 

IF @version2005 = '9.00' 
  BEGIN 
      EXEC Seemigratesqllogins 
  END 
ELSE IF @version2008 = '10.0' 
  BEGIN 
      EXEC Seemigratesqllogins 
  END 
ELSE 
  BEGIN 
      EXEC Sp_help_revlogin 
  END 

IF Object_id ('sp_hexadecimal') IS NOT NULL 
  DROP PROCEDURE sp_hexadecimal 

go 

IF Object_id ('sp_help_revlogin') IS NOT NULL 
  DROP PROCEDURE sp_help_revlogin 

go 

IF Object_id ('seeMigrateSQLLogins') IS NOT NULL 
  DROP PROCEDURE seemigratesqllogins 

go 
/* End Script */ 

No comments:

Post a Comment