Creating logins on a new server, saving the old password

  • I found a good article on how to create a LOGIN in another instance and preserve the password (http://sqlity.net/en/2344/create-login-with-hashed-password/), using:

    select LOGINPROPERTY('<login>','PASSWORDHASH')

    I am about to move a bunch of users over to a new SQL Server instance, and I was trying to script the CREATE LOGIN step. Turns out that the datatype returned during this LOGINPROPERTY call is sql_variant. At least, if I run the following against a user I setup named "junk", the resulting table stores it as SQL_VARIANT:

    select LOGINPROPERTY('junk','PASSWORDHASH') as col1

    into mytmp

    But if I try to convert that to a varchar, so that I can embed it into a string, I don't get what I am expecting:

    select col1,

    cast(col1 as varchar(200)) as cast_to_v,

    convert(varchar(200), col1) as convert_to_v

    from mytmp

    You'll have to actually run this code to see the output. The original column is intact, the other 2 are a single character's worth of a sideways carat

    Ultimately, I want to run something like this within the original database, and then run all resulting SQL in the target instance:

    SELECT 'If not Exists (select loginname from master.dbo.syslogins ' + Char(10)

    + ' where name = '''+ us.name+')' + Char(10)

    + 'Begin ' + Char(10)

    + ' CREATE LOGIN ['+us.name+'] WITH PASSWORD='+CONVERT(VARCHAR(100),LOGINPROPERTY(us.name,'PASSWORDHASH'))+' HASHED, DEFAULT_DATABASE=[r2tgpii], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON ' + Char(10)

    + 'End ' + Char(10)

    + 'GO ' as sqlstmt

    FROM sys.sysusers us right

    JOIN sys.database_role_members rm ON us.uid = rm.member_principal_id

    JOIN sys.database_principals dp ON rm.role_principal_id = dp.principal_id

    I just don't want to have to manually copy & paste each individual's hash into a script. I think I'd just reset everyone's password if that turned out to be the case.

    Thanks for the help,

    --=Chuck

  • sp_help_revlogins will do that for you;

    it produces results like this, because it uses a bin to hex function:

    -- Login: ClarkKent

    IF NOT EXISTS ( SELECT * FROM sys.server_principals WHERE name = 'ClarkKent' )

    CREATE LOGIN [ClarkKent]

    WITH PASSWORD = 0x02006E3299E96343025C2D1958F7545DC4D50232A2024E210BE265724027135CDB54C85A9FCA9DA68231A43C3E285F6F11C8DA6066FE5102BF600F381D2B819390CFA0DE10B8 HASHED,

    SID = 0xEFD437BF4020BA4589532EA675E1208F,

    DEFAULT_LANGUAGE = [us_english],

    CHECK_POLICY = ON,

    CHECK_EXPIRATION = ON;

    here's the version I used on SQL2014, which is more enhanced than the normal version:

    IF OBJECT_ID('[dbo].[sp_help_revlogin_roles]') IS NOT NULL

    DROP PROCEDURE [dbo].[sp_help_revlogin_roles]

    GO

    CREATE PROCEDURE sp_help_revlogin_roles @login_name SYSNAME=NULL,

    @databases BIT=1,

    @roles BIT=1

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @name SYSNAME

    DECLARE @role 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 @is_policy_checked VARCHAR (3)

    DECLARE @is_expiration_checked VARCHAR (3)

    DECLARE @defaultdb SYSNAME

    DECLARE @defaultlang SYSNAME

    DECLARE @crlf VARCHAR(2)

    DECLARE @return INT

    SET @crlf = Char(13) + Char(10)

    IF Object_id('tempdb.[dbo].[#Results]') IS NOT NULL

    DROP TABLE [dbo].[#Results]

    CREATE TABLE [dbo].[#Results]

    (

    [ResultsID] INT IDENTITY(1, 1) NOT NULL,

    [ResultsText] VARCHAR(max) NULL

    )

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '/* sp_help_revlogin script '

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '** Generated '

    + CONVERT (VARCHAR, Getdate()) + ' on '

    + @@SERVERNAME + ' */'

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ''

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '/* Begin Script Logins ------------------------- */'

    IF ( @login_name IS NULL )

    BEGIN

    DECLARE rev_cursor CURSOR STATIC READ_ONLY FOR

    SELECT p.sid,

    p.name,

    p.type,

    p.is_disabled,

    ISNULL(p.default_database_name, 'master'),

    ISNULL(p.default_language_name, 'us_english'),

    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'

    END

    ELSE

    BEGIN

    DECLARE rev_cursor CURSOR STATIC READ_ONLY FOR

    SELECT p.sid,

    p.name,

    p.type,

    p.is_disabled,

    ISNULL(p.default_database_name, 'master'),

    ISNULL(p.default_language_name, 'us_english'),

    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

    END

    OPEN rev_cursor

    FETCH NEXT FROM rev_cursor INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin

    IF ( @@FETCH_STATUS = -1 )

    BEGIN

    INSERT INTO [#Results]

    (ResultsText)

    SELECT 'No login(s) found.'

    SELECT @return = -1

    GOTO Quit

    END

    WHILE ( @@FETCH_STATUS = 0 )

    BEGIN

    SELECT @name = Ltrim(Rtrim(@name))

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '-- Login: ' + @name

    IF ( @type IN ( 'G', 'U' ) )

    BEGIN -- NT authenticated account/group

    INSERT INTO [#Results]

    (ResultsText)

    SELECT 'IF NOT EXISTS ( SELECT * FROM sys.server_principals WHERE name = '''

    + @name + ''' )'

    INSERT INTO [#Results]

    (ResultsText)

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

    INSERT INTO [#Results]

    (ResultsText)

    SELECT 'IF NOT EXISTS ( SELECT * FROM sys.server_principals WHERE name = '''

    + @name + ''' )' + @crlf + ' CREATE LOGIN '

    + Quotename( @name ) + @crlf

    + ' WITH PASSWORD = ' + @PWD_string

    + ' HASHED, ' + @crlf + ' SID = '

    + @SID_string + ', ' + @crlf

    + ' DEFAULT_LANGUAGE = ['

    + @defaultlang + ']' + CASE WHEN ( @is_policy_checked IS NOT NULL ) THEN ',' + @crlf + ' CHECK_POLICY = ' + @is_policy_checked END + CASE WHEN ( @is_expiration_checked IS NOT NULL ) THEN ',' + @crlf + ' CHECK_EXPIRATION = ' + @is_expiration_checked END + ';'

    END

    IF ( @denylogin = 1 )

    BEGIN -- login is denied access

    INSERT INTO [#Results]

    (ResultsText)

    SELECT 'DENY CONNECT SQL TO ' + Quotename( @name )

    END

    ELSE IF ( @hasaccess = 0 )

    BEGIN -- login exists but does not have access

    INSERT INTO [#Results]

    (ResultsText)

    SELECT 'REVOKE CONNECT SQL TO ' + Quotename( @name )

    END

    IF ( @is_disabled = 1 )

    BEGIN -- login is disabled

    INSERT INTO [#Results]

    (ResultsText)

    SELECT 'ALTER LOGIN ' + Quotename( @name ) + ' DISABLE'

    END

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ' '

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ' '

    FETCH NEXT FROM rev_cursor INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin

    END

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '/* End Script Logins ------------------------- */'

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ' '

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ' '

    IF @databases = 1

    BEGIN

    FETCH FIRST FROM rev_cursor INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '/* Begin Script Default Databases ------------------------- */'

    WHILE ( @@FETCH_STATUS = 0 )

    BEGIN

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '-- Login: ' + @name

    INSERT INTO [#Results]

    (ResultsText)

    SELECT 'IF EXISTS ( SELECT * FROM sys.server_principals WHERE name = '''

    + @name + ''' )'

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ' ALTER LOGIN ' + Quotename( @name )

    + ' WITH DEFAULT_DATABASE = [' + @defaultdb

    + ']'

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ' '

    FETCH NEXT FROM rev_cursor INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin

    END

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '/* End Script Default Databases ------------------------- */'

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ' '

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ' '

    END

    CLOSE rev_cursor

    DEALLOCATE rev_cursor

    IF @roles = 1

    BEGIN

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '/* Begin Script Roles ------------------------- */'

    DECLARE rev_cursor CURSOR STATIC READ_ONLY FOR

    SELECT p1.name role_principal_name,

    p2.name member_principal_name

    FROM sys.server_role_members rm

    INNER JOIN sys.server_principals p1

    ON p1.principal_id = rm.role_principal_id

    INNER JOIN sys.server_principals p2

    ON p2.principal_id = rm.member_principal_id

    WHERE p2.type IN ( 'S', 'G', 'U' )

    AND p2.name <> 'sa'

    ORDER BY p2.principal_id

    OPEN rev_cursor

    FETCH NEXT FROM rev_cursor INTO @role, @name

    IF ( @@FETCH_STATUS = -1 )

    BEGIN

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '-- No role member(s) found.'

    END

    WHILE ( @@FETCH_STATUS = 0 )

    BEGIN

    INSERT INTO [#Results]

    (ResultsText)

    SELECT 'EXEC master.dbo.sp_addsrvrolemember @loginame='''

    + @name + ''', @rolename=''' + @role + ''''

    FETCH NEXT FROM rev_cursor INTO @role, @name

    END

    INSERT INTO [#Results]

    (ResultsText)

    SELECT '/* End Script Roles ------------------------- */'

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ' '

    INSERT INTO [#Results]

    (ResultsText)

    SELECT ' '

    CLOSE rev_cursor

    DEALLOCATE rev_cursor

    END

    SELECT ResultsText

    FROM #Results

    ORDER BY ResultsID

    SELECT @return = 0

    QUIT:

    RETURN @return

    END

    GO

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • That's right on the mark. Thanks a million.

    --=Chuck

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply