Fix user and login synchronization after moving database from sql server 2000 to sql server 2005

Wednesday, April 22, 2009 |

When I have moved my SQL Server 2000 database to SQL Server 2005 first time in past, I faced user and login synchronization problem. I want to share it with you as I have seen this question in forums many times.
When you move your database from SQL Server 2000 to SQL Server 2005, logins and users of SQL Server 2000 will be out of sync in SQL Server 2005. This is really very annoying. After digging I came to know what the problem is and I have developed one small script which is very useful and handy to make your login and user in sync back in your new server.
USE AdventureWorks --give your own database name which you have
--restored from SQL Server 2000 to SQL Server 2005
GO
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
Enjoy Upgrading!!!!
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

0 comments: