April 9, 2014 at 4:03 am
Hi all,
Is this feasible via the SSIS Transfer Logins task?? I've read a lot of articles talking about doing this scriptually instead, now I'd far rather have a simple solution that just works than go into the guts of the hashing/encryption between 2K5 and 2K8....
The tool certainly looks promising, but if it's not going to work, I'd far rather it fail entirely rather than leave a bunch of logins I'll have to clean up manually, and how messy THAT could get with SID's and whatnot...
Cheers,
Jake.
April 9, 2014 at 4:25 am
I have not tried this so cannot confirm it it works. Even if it does I can say it would randomize SQL authenticated passwords, so it would be of no use to you if you have any SQL Ids to transfer.
I would use sp_help_revlogin, if you google that there is an 2000 to 2005 version on the MS site which would work for you.
If you cannot find it here is the code
--exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
--GO
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_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005
@login_name sysname = NULL,
@include_db bit = 1,
@include_role bit = 1
AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (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 STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
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 ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'
WHILE @@fetch_status = 0
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 = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END
SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
---------------------------------------------------------------------
April 9, 2014 at 4:34 am
Yes you can do it by SSIS and with script too refer following link for script:
http://support.microsoft.com/kb/918992
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 9, 2014 at 5:21 am
george sibbald (4/9/2014)
I have not tried this so cannot confirm it it works. Even if it does I can say it would randomize SQL authenticated passwords, so it would be of no use to you if you have any SQL Ids to transfer.I would use sp_help_revlogin, if you google that there is an 2000 to 2005 version on the MS site which would work for you.
If you cannot find it here is the code
--exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1--GO
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
.....
Ok, let me get this straight...
1) Use the above code to output the logins/sids/pwords;
2) Use some other code to restore them onto a 2K5 box
3) Use some other code to output the logins/sids/pwords;
4) Use some other code to restore them onto the final 2K8 box.
Is that about right?
Also, by 'SQL" id's do you mean Database users, or windows logins that are also authenticated by SQL (or both!) ?
April 9, 2014 at 5:31 am
your question said transfer from sql2000 (2K) to sql2008 (2K8), is that correct?
SQL IDs are logins authenticated by SQL, i.e. you enter an ID and a password, rather than windows authenticated logins where you just pass your domain ID credentials - so your instance is running in mixed mode.
---------------------------------------------------------------------
April 9, 2014 at 5:40 am
Jake Shelton (4/9/2014)
Hi all,Is this feasible via the SSIS Transfer Logins task?? I've read a lot of articles talking about doing this scriptually instead, now I'd far rather have a simple solution that just works than go into the guts of the hashing/encryption between 2K5 and 2K8....
The tool certainly looks promising, but if it's not going to work, I'd far rather it fail entirely rather than leave a bunch of logins I'll have to clean up manually, and how messy THAT could get with SID's and whatnot...
Cheers,
Jake.
I tend to use my own script, it doesnt catch the logins roles but it will script the SID and hashed password
SELECT'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
sys.fn_varbintohexstr(password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +
', DEFAULT_LANGUAGE = ' + default_language_name +
', CHECK_EXPIRATION = ' +
CASE
WHEN is_expiration_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
', CHECK_POLICY = ' +
CASE
WHEN is_policy_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
CASE is_disabled
WHEN 0 THEN ''
ELSE '; ALTER LOGIN [' + name + '][ DISABLE;'
END
FROM master.sys.sql_logins
WHERE name not like '##%' and name <> 'sa'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2014 at 10:01 am
Jake Shelton (4/9/2014)
Hi all,Is this feasible via the SSIS Transfer Logins task?? I've read a lot of articles talking about doing this scriptually instead, now I'd far rather have a simple solution that just works than go into the guts of the hashing/encryption between 2K5 and 2K8....
The tool certainly looks promising, but if it's not going to work, I'd far rather it fail entirely rather than leave a bunch of logins I'll have to clean up manually, and how messy THAT could get with SID's and whatnot...
Cheers,
Jake.
Don't rely on SSIS. Use this instead (Method 2).
http://support.microsoft.com/kb/246133
This doen't transfer Server roles. You need to check that manually.
--
SQLBuddy
April 10, 2014 at 2:12 am
george sibbald (4/9/2014)
your question said transfer from sql2000 (2K) to sql2008 (2K8), is that correct?SQL IDs are logins authenticated by SQL, i.e. you enter an ID and a password, rather than windows authenticated logins where you just pass your domain ID credentials - so your instance is running in mixed mode.
Yep, the migration is from 2000 to 2008, and we are indeed running mixed mode. My question is whether I need to move the logins to a 2k5 box as an interim step before relocating again to 2008.
April 10, 2014 at 3:35 am
Jake Shelton (4/10/2014)
george sibbald (4/9/2014)
your question said transfer from sql2000 (2K) to sql2008 (2K8), is that correct?SQL IDs are logins authenticated by SQL, i.e. you enter an ID and a password, rather than windows authenticated logins where you just pass your domain ID credentials - so your instance is running in mixed mode.
Yep, the migration is from 2000 to 2008, and we are indeed running mixed mode. My question is whether I need to move the logins to a 2k5 box as an interim step before relocating again to 2008.
then you need the sp_help_revlogin_2000_to_2005 specifically that I posted (its the method2 in the link sqlbuddy referred to). SQL2005 and 2008 did not change so you can use this to go direct to 2008, you do not need an intermediate step.
run the code, copy the results over to your destination server, copy into SSMS and run in the logins you need. I presume this is a one off operation so no need to automate.
All you will be missing then potentially is any server roles and the default language setting. These two bits of code run on the SQL2000 server will reverse engineer those for you.
set quoted_identifier off
set nocount on
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'sysadmin'"
from syslogins where sysadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'securityadmin'"
from syslogins where securityadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'serveradmin'"
from syslogins where serveradmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'setupadmin'"
from syslogins where setupadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'processadmin'"
from syslogins where processadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'diskadmin'"
from syslogins where diskadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'dbcreator'"
from syslogins where dbcreator = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'bulkadmin'"
from syslogins where bulkadmin = 1
set quoted_identifier off
select 'exec sp_defaultlanguage ',+"'" +loginname +"'," +language from syslogins
---------------------------------------------------------------------
April 10, 2014 at 6:14 am
Jake Shelton (4/10/2014)
My question is whether I need to move the logins to a 2k5 box as an interim step before relocating again to 2008.
No you don't and if youre not concerned with server roles my script is perfect
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2014 at 7:26 am
Perry Whittle (4/10/2014)
Jake Shelton (4/10/2014)
My question is whether I need to move the logins to a 2k5 box as an interim step before relocating again to 2008.No you don't and if youre not concerned with server roles my script is perfect
One thing I like about Perry's script is exclusion of '##%' and 'SA'. You might face some issue if you execute this script for 'SA' password will change to from where you have copied the login. And what if you forget to change?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 10, 2014 at 7:33 am
Perry Whittle (4/10/2014)
Jake Shelton (4/10/2014)
My question is whether I need to move the logins to a 2k5 box as an interim step before relocating again to 2008.No you don't and if youre not concerned with server roles my script is perfect
It may well be, but I possibly neglected to mention that I need the passwords to be ported across too...if memory serves, without the 2K5 step, the hashing will fail as it has changed base algorithm between 2000 and 2005.
April 10, 2014 at 7:40 am
Jake, the sp_help_revlogin will bring passwords across. straight to 2008. I have done it myself many times.
Perry, master.sys.sql_logins is not available in SQL2000 so Jake will not be able to reverse engineer his login details with your script.
---------------------------------------------------------------------
April 10, 2014 at 1:32 pm
Jake Shelton (4/10/2014)
if memory serves, without the 2K5 step, the hashing will fail as it has changed base algorithm between 2000 and 2005.
Step not required. The algorithm between 2008 and 2012 has changed.
george sibbald (4/10/2014)
Jake, the sp_help_revlogin will bring passwords across. straight to 2008. I have done it myself many times.Perry, master.sys.sql_logins is not available in SQL2000 so Jake will not be able to reverse engineer his login details with your script.
My bad, this is the correct script for SQL2000
SELECT'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
dbo.fn_varbintohexstr(password) +
' HASHED, SID = ' + dbo.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(DB_NAME(dbid)) +
', DEFAULT_LANGUAGE = ' + language +
'; ALTER LOGIN [' + name + '] DISABLE;'
FROM master.dbo.sysxlogins
WHERE name <> 'sa' AND password IS NOT NULL
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 14, 2014 at 7:58 am
george sibbald (4/10/2014)
Jake, the sp_help_revlogin will bring passwords across. straight to 2008. I have done it myself many times.Perry, master.sys.sql_logins is not available in SQL2000 so Jake will not be able to reverse engineer his login details with your script.
Worked great, thanks!!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply