Restore Backup of SQL 2000 to SQL 2005 and User Default Schema

Question

Post reply

Restore Backup of SQL 2000 to SQL 2005 and User Default Schema

htt

SSCertifiable

Points: 5242
More actions
September 22, 2006 at 5:30 pm

#113585

We will upgrade our SQL servers from SQL 2000 to SQL 2005 next month. I did a test restoring a backup from SQL 2000 Std server to a SQL 2005 Developer Edition hosted on my XP PC. The restore was OK. However there is one thing that I am not sure if I need to do any thing or just leave it as is. Let's say there is a database user named Steve in the DB on old server SQL 2000. First I create a SQL login named Steve in SQL 2005. I restore the DB from backup, run sp_change_users_login to map database user Steve to SQL login Steve. I can login as Steve to query the database. However the default schema of Steve is Steve. There is no way to change it to DBO or anything else.
Is this OK if I leave the default schema like this? I don't want to run into any unseen problem later with the default schema. Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

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

htt SSCertifiable Points: 5242 More actions · Answer 1

please ingore this post. I made a mistake selecting wrong schema db_owner instead of dbo.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

One step that would make life easier is to create SQL logins on your new server with the same SID as the old server. Then you don't need the sp_change_users_login step after moving a database between servers with backup/restore or detach/attach.

This command runs on a SQL2000 system to generate a SQL2005 CREATE LOGIN statement:

select 'create login ' + rtrim(name) + ' with password=', password, ' hashed, sid=', sid

from master.dbo.sysxlogins where name = '<login>'

Feel free to get creative and add the default database and language options.

htt SSCertifiable Points: 5242 More actions · Answer 3

htt

SSCertifiable

Points: 5242

September 26, 2006 at 11:11 am

#662538

Great advice!

Thanks.

Darren Chrstensen Grasshopper Points: 11 More actions · Answer 4

Here is a bit of code that builds on Scott's statment that will give you the create statments for all the logins. I was inspired by a password checking script written by Randy Dyess at http://www.TransactSQL.Com. It is generating a few errors, but it seems to get most of the way there. I would clean it up but I think I will go to bed instead

Note: xp_varbintohexstr is not supported in 2005.

--Variables

DECLARE @lngCounter INTEGER

DECLARE @lngCounter1 INTEGER

DECLARE @lngLogCount INTEGER

DECLARE @strName VARCHAR(256)

DECLARE @strPW VARCHAR(256)

DECLARE @strSID VARCHAR(256)

DECLARE @binSID binary(16)

DECLARE @binPW binary(46)

--Create table to hold SQL logins

CREATE TABLE #tLogins

(

numID INTEGER IDENTITY(1,1)

,strLogin SYSNAME NULL

,binPW varbinary(46) NULL

,binSID varbinary(16) NULL

)

--Insert non ntuser into temp table

INSERT INTO #tLogins (strLogin, binPW, binSID)

SELECT L.name as name, XL.password as pw, XL.sid as sid FROM master.dbo.syslogins AS L INNER JOIN master.dbo.sysXlogins AS XL ON L.sid = XL.sid WHERE isntname = 0

SET @lngLogCount = @@ROWCOUNT

--select * from #tLogins

SET @lngCounter = @lngLogCount

WHILE @lngCounter 0

BEGIN

SELECT @strName = strLogin, @binPW = binPW, @binSID = binSID FROM #tLogins WHERE numID = @lngCounter

EXEC master..xp_varbintohexstr @binPW, @strPW OUTPUT

EXEC master..xp_varbintohexstr @binSID, @strSID OUTPUT

print 'create login '

+ rtrim(@strName)

+ ' with password='

+ @strPW + ' hashed, sid = ' + @strSID

SET @lngCounter = @lngCounter - 1

END

drop table #tLogins