SQL Server 2005: a proposed update of sp_help_revlogin


The sp_helprevlogin procedure is described in KB article 246133. This procedure generates a script that can be used to recreate the logins that exist on a server at a specific point in time. It can be useful for transferring logins from one server to another. My colleague, Craig Gick, has updated this stored procedure to work with SQL Server 2005. I am posting the updated procedure here in the hope you will find it useful until the KB article gets updated.


Note that to obtain the proper results, the procedure needs to be executed by a sysadmin. This is because only a sysadmin has access to the password hashes for SQL logins. Also, note that the procedure only scripts the statements needed to recreate the logins; it does not script the permissions assigned to the logins – that should be done separately.


The procedure is using the new SQL Server 2005 CREATE LOGIN DDL and sys.server_principals catalog view, as well as the new loginproperty builtin. There are no changes made to the system; the procedure is just collecting information and priniting the script as output.


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 (514) OUTPUT


AS


DECLARE @charvalue varchar (514)


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 @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)


 


IF (@login_name IS NULL)


  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin


        FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )


        WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’


ELSE


  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin


        FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )


        WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name


OPEN login_curs


FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @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’


    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


 


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


 


    PRINT @tmpstr


  END


  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin


  END


CLOSE login_curs


DEALLOCATE login_curs


 


RETURN 0


GO



 


Comments (13)

  1. Eric Newton says:

    So why doesnt Sql Server just use logins defined in the databases attached?

    Why is it that Sql Server simply must transfer logins?  I can see a point where a particular login needs access to multiple databases.  However, more and more this simply isnt the norm… I myself havent used ANY databases that have logins that need “inter-database” access, so why not just have the Server enumerate logins defined at the database level and soft-transfer them into its own auth routine?

    Just seems backwards to me…

  2. Sharon Dooley says:

    Well, it will create the logins if you use Copy Database Wizard, and it will move all logins or only those used by the databases being copied.

    But there are cases (log shipping, database mirroring) where you need to have the logins the same but are not attaching the database but using restore. A restore to a second server has no access to the login information from the first server; all that’s stored in the database is the user name and the login SID.

    So this procedure is extremely useful and necessary.

    Sharon

  3. lcris says:

    Also, because logins are server level entities, they are not stored at the database level, so they can’t be moved with the database, as Eric seems to suggest, if I am understanding his question right.

  4. Jon Baker says:

    Looks like MSFT updated the KB article with your script, but they are still referencing this as SQL 2000 information.  Of course this script does not work with SQL 2000.  It might be nice to have a conditional statement to work for both!

    Jon

  5. lcris says:

    Yes, this is an error. The article will be updated. The procedure I posted uses syntax introduced in SQL Server 2005, so it is not possible to write a common version that will work on both SQL Server 2000 and SQL Server 2005. You will need to use a 2000 version when working on 2000 servers and a 2005 version when working with 2005 servers.

  6. edwardp says:

    You could easily have both scripts in one wrapper with a quick check to the version ran against and a goto to send to the right block of code to run. I have modified the sql2000 version to also insert the default database for both win and sql logins it came of the original server with.

    sqlscripters@hotmail.com

    Enjoy:

    CREATE PROCEDURE sp_help_revlogin_Mod @login_name sysname = NULL

    AS  

    –Microsoft Corporation / Public Web Content

    —sp_help_revlogin Modified by edwardp 12/5/2003 to return and print

    — the default database for each login as it generates the script

    DECLARE @name    sysname

    DECLARE @xstatus int

    DECLARE @binpwd  varbinary (255)

    DECLARE @txtpwd  sysname

    DECLARE @tmpstr  varchar (255)

    DECLARE @tmpstr2 varchar (255)

    DECLARE @dbnm varchar (255)

    DECLARE @dbnm2 varchar (255)

    IF (@login_name IS NULL)

    –We needed to pull the dbid from the sys table

     DECLARE login_curs CURSOR FOR

       SELECT name, xstatus, password, dbid FROM master..sysxlogins

       WHERE srvid IS NULL AND name <> ‘sa’

    ELSE

     DECLARE login_curs CURSOR FOR

       SELECT name, xstatus, password, dbid FROM master..sysxlogins

       WHERE srvid IS NULL AND name = @login_name

    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @name, @xstatus, @binpwd, @dbnm

    IF (@@fetch_status = -1)

    BEGIN

     PRINT ‘No login(s) found.’

     CLOSE login_curs

     DEALLOCATE login_curs

     RETURN -1

    END

    SET @tmpstr = ‘/* sp_help_revlogin_Mod 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

    –We use a function to convert the db_id to the DB_NAME since

    –the sp’s do not accept the db id as an argument

    –We only want valid logins so at the NT login has access is where we apply the fix

    Select @dbnm2 = DB_NAME(@dbnm)

       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

    SET @tmpstr2 = ‘EXEC master..sp_defaultdb ”’ + @name + ”” + ‘,’ + ‘ ‘ + ”” + @dbnm2 + ””

    Print ‘– Needed to Set Default Db Seperately from the login with a windows login’

    PRINT @tmpstr2

         END

       END

       ELSE BEGIN — SQL Server authentication

    –SQL logins both null passwords and valid are fixed here

         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, ‘ + @txtpwd + ‘)’

           ELSE

             SET @tmpstr = ‘SET @pwd = CONVERT (varbinary, ‘ + @txtpwd + ‘)’

           PRINT @tmpstr

           SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name

             + ”’, @pwd ‘ + ‘,’ + ‘ ‘ + ”” + @dbnm2 + ””+’,’+’ @encryptopt = ‘

         END

         ELSE BEGIN

           — Null password

           SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name

             + ”’, NULL’ + ‘,’ + ‘ ‘ + ”” + @dbnm2 + ””+’,’+’ @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 @name, @xstatus, @binpwd, @dbnm

     END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

    GO

  7. lcris says:

    You’re right, I stand corrected: it is possible to combine both implementations as long as the use of new syntax is restricted to dynamic SQL. If you want to do this with the implementation I posted, you would need to rewrite the use of the loginproperty builtin, because the current use would not allow you to create the procedure in SQL Server 2000.

  8. edwardp says:

    Ah, seems we are both right.

  9. Home Brew says:

    Is there a way to script the database permissions for each login ?

  10. lcris says:

    You can extract the permissions from the sys.server_permissions catalog, but I don’t know of a procedure or tool that already does this.

    Ideally, the database administrator should maintain such a script to record all permission operations.

  11. SQLgiant says:

    Thanks for providing an answer regarding the Error in KB article 246133 that still references the sp_help_rev_Login version as a SQL2000 script, which it clearly is not.

    A link in the article to the old SQL2000 script would be a decent fix, and a comment in the script itselt would be useful. For some time, many will need both versions, as I do now. Guess I will go fishing on my old servers…

  12. edwardp says:

    I use this as a stand alone type script now not a proc since I pull it into a VB Script that parses a txt with server names which it then goes to each building

    the revlogin script 1 per server. I confirmed this works for SQL7 also but not SQL2005 which I think a new one would be better to capture some of the new

    password options. To use as a standalone remove create, Declare the login_name variable and comment out the Returns.

    COPY BELOW HERE:

    –if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin2k_Mod]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)

    –drop procedure [dbo].[sp_help_revlogin2k_Mod]

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    CREATE PROCEDURE sp_help_revlogin2k_Mod @login_name sysname = NULL AS

    DECLARE @name    sysname

    DECLARE @xstatus int

    DECLARE @binpwd  varbinary (256)

    DECLARE @txtpwd  sysname

    DECLARE @tmpstr  varchar (256)

    DECLARE @tmpstr2 varchar (255)

    DECLARE @dbnm varchar (255)

    DECLARE @dbnm2 varchar (255)

    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, dbid FROM master..sysxlogins

       WHERE srvid IS NULL AND name <> ‘sa’

    ELSE

     DECLARE login_curs CURSOR FOR

       SELECT sid, name, xstatus, password, dbid 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, @dbnm

    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

    –We use a function to convert the db_id to the DB_NAME since

    –the sp’s do not accept the db id as an argument

    –We only want valid logins so at the NT login has access is where we apply the fix

    Select @dbnm2 = DB_NAME(@dbnm)

       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

    SET @tmpstr2 = ‘EXEC master..sp_defaultdb ”’ + @name + ”” + ‘,’ + ‘ ‘ + ”” + @dbnm2 + ””

    Print ‘– Needed to Set Default Db Seperately from the login with a windows login’

    PRINT @tmpstr2

         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’ + ‘,’ + ‘ ‘ + ”” + @dbnm2 + ””+’,’+’ @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’ + ‘,’ + ‘ ‘ + ”” + @dbnm2 + ””+’,’+’ @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, @dbnm

     END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

    GO

    —– End Script —–

  13. Neeraj says:

    We came across the situation multiple times when we were migrating a single database and had to migrate logins only related to that database. I made a minor tweak to the stored procedure to accept database name as a parameter.

    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 (514) OUTPUT

    AS

    DECLARE @charvalue varchar (514)

    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_revlogin2') IS NOT NULL

     DROP PROCEDURE sp_help_revlogin2

    GO

    CREATE PROCEDURE sp_help_revlogin2 @login_name sysname = NULL, @db_name sysname = NULL

    AS

    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)

    — The code below this has been changed from original sp_help_revlogin stored proc

    declare @sql varchar(500)

    IF (@login_name IS NULL and @db_name is NULL)

     DECLARE login_curs CURSOR FOR

         SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin

           FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )

           WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

    ELSE

    IF (@login_name IS NOT NULL)

     DECLARE login_curs CURSOR FOR

         SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin

           FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )

           WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

    ELSE

    IF (@db_name is not null)

           begin

           select @sql='DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin

           FROM sys.server_principals p LEFT JOIN sys.syslogins l

           ON ( l.name = p.name )

           join '+@db_name+'.sys.database_principals d

           on d.sid=p.sid

           WHERE p.type IN ( ''S'', ''G'', ''U'' ) AND p.name <> ''sa''

           order by p.name'

           exec (@sql)

           end

    — The code above this line has been changed from original sp_help_revlogin stored proc

    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @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'

       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

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

       PRINT @tmpstr

     END

     FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin

     END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

    GO