SQL Login Transfer from SQL2000 to SQL2005

Hello,

I am trying to automate SQL Login transfer from SQL2000 to SQL2005.
The SQL2000 database is restored onto SQL2005 server every night.
Once the db is restored, the db users will be orphaned.
The following KB tells you how to transfer login with password but it doesn't seem to restore database mapping and its permission.
http://support.microsoft.com/default.aspx?kbid=246133

Any help will be appreciated.
JOSHUABTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris LuttrellSenior Database ArchitectCommented:
here is a script I use to reset the users after restoring a backup from another DB and the users not matching up anymore.  See if it works for you.  You should be able to script this to run as part of your nightly process.
declare @U Table (UserName nVarchar(100), UserSid nvarchar(100))
declare @uName nvarchar(100), @ss nvarchar(500)
 
exec sp_change_users_login Report
 
insert into @u
exec sp_change_users_login Report
 
--select UserName from @u
 
while exists (select * from @u)
Begin
	select top 1 @uName=UserName from @u
	IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @uName)
	BEGIN
		select @ss='DROP USER '+ UserName from @u where UserName = @uName
		print @ss + ' because of no Login Account'
		--exec sp_executesql @ss 
	END
	ELSE
	BEGIN
		select @ss='sp_change_users_login Auto_Fix, '+ UserName from @u where UserName = @uName
		print @ss
		exec sp_executesql @ss 
	END
	delete	from @u where UserName = @uName
End
 
exec sp_change_users_login Report

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RiteshShahCommented:
once you transfer your DB, run below short and easy script to "Auto Fix" your orphan user.



DECLARE @UserName Nvarchar(50)
DECLARE c CURSOR DYNAMIC
FOR SELECT DISTINCT [Name] FROM [sysusers] WHERE [sysusers].[uid] < 16000 AND [Name] NOT IN ('guest', 'sa', 'dbo','public','sys','INFORMATION_SCHEMA') ORDER BY [Name]
OPEN c
FETCH NEXT FROM c INTO @UserName
WHILE @@FETCH_STATUS=0
BEGIN
EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL, @UserName
FETCH NEXT FROM c INTO @UserName
SELECT @UserName
END
CLOSE c
DEALLOCATE c
GO

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.