Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Transfer of Logins from 2k to 2K8 Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 4:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:49 AM
Points: 163, Visits: 831
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.
Post #1559854
Posted Wednesday, April 9, 2014 4:25 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:21 PM
Points: 5,975, Visits: 12,883
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



---------------------------------------------------------------------

Post #1559858
Posted Wednesday, April 9, 2014 4:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1559860
Posted Wednesday, April 9, 2014 5:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:49 AM
Points: 163, Visits: 831
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

[code="sql"]--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!) ?
Post #1559879
Posted Wednesday, April 9, 2014 5:31 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:21 PM
Points: 5,975, Visits: 12,883
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.


---------------------------------------------------------------------

Post #1559880
Posted Wednesday, April 9, 2014 5:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 6,186, Visits: 13,335
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"
Post #1559884
Posted Wednesday, April 9, 2014 10:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 1,194, Visits: 2,211
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
Post #1560043
Posted Thursday, April 10, 2014 2:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:49 AM
Points: 163, Visits: 831
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.
Post #1560281
Posted Thursday, April 10, 2014 3:35 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:21 PM
Points: 5,975, Visits: 12,883
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



---------------------------------------------------------------------

Post #1560318
Posted Thursday, April 10, 2014 6:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 6,186, Visits: 13,335
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"
Post #1560384
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse