How to Copy SQL 2000 Databases and Logins to SQL 2005
If you have attempted to copy a SQL 2000 database and it's logins to a SQL 2005 server, you may have noticed that the local SQL logins can no longer login to the database after it has been copied due to mismatched SID's.
I had a requirement to copy several SQL 2000 databases with hundreds of local logins and found many of the logins could not login to the database after it had been copied to SQL 2005. After working with Microsoft we came up with the following resolution to this issue, this solution will work no matter if there are just a few logins or hundreds of them. The following is broken up into three sections, the instructions, and two Stored Procedures.
http://support.microsoft.com/kb/246133/
-- **************** Instructions ****************
1. Login or connect to a SQL 2005 server instance, and connect to the Database Engine
2. From within the SQL 2005 database management studio, connect to a SQL 2000 instance
3. Expand the databases tree on your SQL 2000 instance
4. Right click the database you are interested in copying and select New Query
5. Copy, and paste the Stored Procedure below calledsp_hexadecimal.sqlinto the new query window and execute it
6. Clear the stored procedure from the new query window
7. Copy, and paste the Stored Procedure below calledsp_help_revlogin_by_db.sqlinto the new query window butdo not execute it
8. There are two lines near the top of the stored procedure called MyDatabase, change both of these to your actual database name and then execute it
9. Refresh your SQL 2000 database, and then expand the Programmability>Stored Procedures tree to expose the two stored procedures
10. Right click the Stored Procedure called sp_help_revlogin_new and select Script Stored Procedure as>Execute to>New Query Editor Window and execute it
11. Copy the output in the bottom window to a Notepad document
12. In your SQL 2005 database instance, right click any databaseother than the system databases
13. Select Tasks>Copy Database
14. Follow the wizard all the way through to copy your SQL 2000 database butdo not import the Logins
15. After the database has been copied, refresh your SQL 2005 instance so that your new database is present
16. Right click your new database and select New Query
17. Copy, paste and execute the information in the Notepad document you saved earlier
18. Your local SQL Logins should now be able to connect and login to the database
19. Drop both Stored Procedures from the database on both of the SQL 2000 and SQL 2005 instances
20. Note, if you try to test this multiple times you may receive an error when attempting to reapply the local user permissions after the database has been copied. Make sure that before you attempt to run this again, the local user logins are deleted in the SQL 2005 instance under Security>Logins at the SQL Server level, not the database level.
-- *********************sp_hexadecimal.sql **********************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_hexadecimal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_hexadecimal]
GO
drop procedure [dbo].[sp_hexadecimal]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
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
@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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- *****************************sp_help_revlogin_by_db.sql**************************
CREATE PROCEDURE sp_help_revlogin_new @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (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 FOR
SELECT m.sid, m.name, xstatus, m.password FROM master..sysxlogins M,MyDatabase..sysusers T
WHERE M.sid=T.sid and (srvid IS NULL AND m.name <> 'sa')
ELSE
DECLARE login_curs CURSOR FOR
SELECT m.sid, m.name, xstatus, m.password FROM master..sysxlogins M,MyDatabase..sysusers T
WHERE M.sid=T.sid and (srvid IS NULL AND m.name = @login_name)
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
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 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
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 = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
DECLARE login_curs CURSOR FOR
SELECT m.sid, m.name, xstatus, m.password FROM master..sysxlogins M,MyDatabase..sysusers T
WHERE M.sid=T.sid and (srvid IS NULL AND m.name <> 'sa')
ELSE
DECLARE login_curs CURSOR FOR
SELECT m.sid, m.name, xstatus, m.password FROM master..sysxlogins M,MyDatabase..sysusers T
WHERE M.sid=T.sid and (srvid IS NULL AND m.name = @login_name)
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
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 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
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 = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
-- ***************** END Process *******************
Give it a try and see if it works for you
[14102 byte] By [Andy_T] at [2007-12-22] 