Migrate SQL 2000 Logins to SQL 2005

Question

Migrate SQL 2000 Logins to SQL 2005

Viewing 15 posts - 16 through 30 (of 34 total)

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

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 1

[font="Verdana"]You can also make use of Transfer Login SSIS task. Using this you can transfer logins from SQL 2000 to SQL 2005.

Refer the link below

Transfer Logins Task[/font]

Regards..Vidhya Sagar
SQL-Articles

HeftyP SSC Enthusiast Points: 197 More actions · Answer 2

HeftyP

SSC Enthusiast

Points: 197

June 30, 2008 at 6:53 am

#836071

I used the above artice and it worked perfectly.

Brad-805074 SSC Enthusiast Points: 166 More actions · Answer 3

Hi Ed,

Please could you send me the transfer login procedure doc for transfering logins from sql 2000 to 2005?

brad.forrest @ za.didata.com

Thanks,

Brad

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 4

I recently migrated databases from sql 2000 to sql 2005, the logins too got migrated, however I had to use these SPs to fix or map the logins that were newly moved:

EXEC sp_change_users_login 'Report'--this will show us how many logins have to be mapped to the new server. that is it will show us the ophaned users.

Now, if you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'userid'

this procedure worked, hope u could find useful.

thanks,

psnemade SSC Enthusiast Points: 138 More actions · Answer 5

psnemade

SSC Enthusiast

Points: 138

January 14, 2009 at 11:40 pm

#927088

Can you send me login migration stored proc.

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 6

Here are they,

http://support.microsoft.com/kb/246133

http://support.microsoft.com/kb/918992

- Deepak

[font="Verdana"]- Deepak[/font]

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

Wow ... all kinds of solutions in this thread !! .... now if I can figure out which is "best"

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 8

There is no "best"

You should look through the list of solutions, pick one that makes sense to you, you understand, and you can easily implement.

AAkula SSCertifiable Points: 5394 More actions · Answer 9

Hi,

Iam transfering logins from sql 2000 to 2005.Sql 2000 and sql 2005 are in different domains.

So Iam using the procedure exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1

GO from the link http://support.microsoft.com/kb/246133.

and in the output generated by this procedure we need to modify the domain name and run in sql 2005 as KB Artile saying

Remarks
Review the output script carefully before you run it on the destination SQL Server. If you have to transfer logins to an instance of SQL Server in a different domain than the source instance of SQL Server, edit the script generated by the sp_help_revlogin procedure, and replace the domain name with the new domain in the sp_grantlogin statements

But in the output script generated by this procedure I did NOT have the sp_grantlogin statements to change the domain name.

Please advice me where Iam doing the mistake???

AAkula SSCertifiable Points: 5394 More actions · Answer 10

I do not have sp_grant logins statement in the out put script generated by sp_help_revlogin. Is this because of I having all logins as sql server authentication ? I just have BUILTIN\ADMINSTRATORS as the windows login.

please advice me...

Thanks

Kotla

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 11

If you're dealing with SQL Server 2000 and below, here's the query that will reveal the Windows logins you have:

SELECT name FROM syslogins WHERE isntname = 1;

For SQL Server 2005 and above, use this:

SELECT name FROM sys.server_principals WHERE TYPE IN ('U', 'G');

If you're only seeing BUILTIN\Administrators, then that would explain why you don't see any more sp_grantlogins. But you should be at least seeing BUILTIN\Administrators as an sp_grantlogin.

K. Brian Kelley
@kbriankelley

AAkula SSCertifiable Points: 5394 More actions · Answer 12

Thanks Brain,

As you are saying I should have atleast one sp_grantlogin as we have one windows login. But clearly Iam not getting sp_grantlogin statement in output script generated by sp_help_revlogin.

Here is the output script generated by the sp_help_revlogin:

/* sp_help_revlogin script

** Generated Feb 22 2009 2:03PM on ABC */

/***** CREATE LOGINS *****/

-- Login: ABC

CREATE LOGIN [ABC] WITH PASSWORD=0x0100B2..... HASHED, CHECK_POLICY=OFF, SID=0x6EF644C639B66E498039DB0FEDA0E4A1

-- Login: ABCD

CREATE LOGIN [ABCD] WITH PASSWORD=0x0100BE......HASHED, CHECK_POLICY=OFF, SID=0xF59265B39109FD4999FABAAF242B2334

-- Login: ABCDE

CREATE LOGIN [ABCDE] WITH PASSWORD='', CHECK_POLICY=OFF, SID=0x3CEC295DFB32C342A0295

-- Login: ABCDEF

CREATE LOGIN [ABCDEF] WITH PASSWORD=0x0100C93D70370ED9ABF7CA3C1763B6 HASHED, CHECK_POLICY=OFF, SID=0x3922A096AD89EB49B6BA68362BFDA1B6

-- Login: BUILTIN\Administrators

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'BUILTIN\Administrators')

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS

-- Login: ABCDEFG

CREATE LOGIN [ABCDEFG] WITH PASSWORD=0x01001540CD74D2D0A7552 HASHED, CHECK_POLICY=OFF, SID=0x70C28054996CAF41B1F9A375BD6DD9B7

/***** SET DEFAULT DATABASES *****/

-- Login: ABC

ALTER LOGIN [ABC] WITH DEFAULT_DATABASE=[MASTER]

-- Login: ABCD

ALTER LOGIN [ABCD] WITH DEFAULT_DATABASE=[master]

-- Login: ABCDE

ALTER LOGIN [ABCDE] WITH DEFAULT_DATABASE=[master]

-- Login:ABCDEF

ALTER LOGIN [ABCDEF] WITH DEFAULT_DATABASE=[master]

-- Login: BUILTIN\Administrators

ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=[master]

-- Login: ABCDEFG

ALTER LOGIN [ABCDEFG] WITH DEFAULT_DATABASE=[master]

/***** SET SERVER ROLES *****/

-- Login: ABC

exec master.dbo.sp_addsrvrolemember @loginame='ABC', @rolename='sysadmin'

-- Login: ABCD

-- Login: ABCDE

-- Login: ABCDEF

-- Login: BUILTIN\Administrators

exec master.dbo.sp_addsrvrolemember @loginame='BUILTIN\Administrators', @rolename='sysadmin'

-- Login: ABCDEFG

exec master.dbo.sp_addsrvrolemember @loginame='ABCDEFG', @rolename='sysadmin'

So, There is nothing to change the domain name in this script. Is it safe to run this script as it is in sql 2005 OR do I need to change anything?

Note: Sql 2000 and sql 2005 are in different domains

Thanks for your help

Kotla

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 13

It's using the proper T-SQL command for SQL Server 2005 and above. You can see it here:

-- Login: BUILTIN\Administrators

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'BUILTIN\Administrators')

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS

The sp_addlogin and sp_grantlogin stored procedures are deprecated, as are sp_grantdbaccess (use CREATE USER now).

K. Brian Kelley
@kbriankelley

AAkula SSCertifiable Points: 5394 More actions · Answer 14

thanks Brain,

I just copy the output script generated by sp_help_revlogin in sql 2000 and ran it in sql 2005, which is in different domain and the script ran succesfully and logins were created in sql 2005. Its means that I have no Issues with logins right?

please clarify me this..

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 15

If the only Windows group was BUILTIN\Administrators, this is a Windows group that is local to the physical server. In that case, being in a different domain shouldn't matter at all. If, however, there were other Windows logins, the answer is it depends. If you're still using the Windows users and groups from the other domain, then everything is great. If not, if you're migrating domains, you'll probably need to create new logins corresponding to the new domain.

K. Brian Kelley
@kbriankelley