October 28, 2016 at 4:47 pm
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
October 28, 2016 at 7:33 pm
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
October 31, 2016 at 2:40 pm
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