backup (SQL2000)/restore (SQL2005) problem in details

Sorry, I have to give more detail about my question.
I have trouble with it. I have tried several ways and talked to some people
in the forum, but it is not working. The following is waht I did.
(1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
COMPLETE backup setup, owners: dbo. Security:Windows mode
and my login name in database: machine_name\my_login_name. OS: 2000server
(2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
(3) Start SQL Server Management Studio of SQL Server 2005.
(4) Use Restore to restore the backup to a database with same name DB. Auto
generated the user list which is same as in original database server: dbo
(owner), guest, ASPNET and so on. My login name in new server machine,
displayed in security under the node databases: nwe_machine_name\new
loginname (has a space between two words).
(5)Now I add new user to the stored database DB:
Expand Security under DB.
Rigth click on Users and select new user. Dialog pop up.
Broswer Login Name: nwe_machine_name\new loginname
Type in user name: new
Browser default schema: db_owner
Select Database Role Menbership: db_owner
Click OK.
User new is added in the list.
(6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
work. When I click the tree node Database Diagrams, I get
the error massage. I can not either display the diagram or create new
diagram on the database. The error message:
"Database diagram support objects cannot be installed because this database
does not have valid owner.
To continue, first use the Files page of the Database Properties dialog box
ot the Alter Authorization statement
to set the database owner to a valid login, then add the datatbase diagram
support objects."
I can not either show the diagram or create a new diagram on this restored
database DB.
I do not know the detail steps for doing it. MSDN articles suggest that
For compatiable, run
exec sp_dbcmptlevel 'database_name', '90';
(It is OK for me to exec. it)
For alter authorization, run
ALTER AUTHORIZATION ON DATABASE::databasename to new
which produced:
"Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'new', because it does not exist or you do not
have permission."
I have trouble here. I do not know how to do it. Did I do something wrong?
What is the correct way/steps?
Are there any tricks in backup and restore process?
Thank you for any help.
David
Did you copy the user accounts and logins from SQL Server 2000 to 2005?
"david" <david@.discussions.microsoft.com> wrote in message
news:E45E94E2-C4CF-4488-BF6D-D048A4C6F175@.microsoft.com...
> Sorry, I have to give more detail about my question.
> I have trouble with it. I have tried several ways and talked to some
> people
> in the forum, but it is not working. The following is waht I did.
> (1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
> COMPLETE backup setup, owners: dbo. Security:Windows mode
> and my login name in database: machine_name\my_login_name. OS: 2000server
> (2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
> (3) Start SQL Server Management Studio of SQL Server 2005.
> (4) Use Restore to restore the backup to a database with same name DB.
> Auto
> generated the user list which is same as in original database server: dbo
> (owner), guest, ASPNET and so on. My login name in new server machine,
> displayed in security under the node databases: nwe_machine_name\new
> loginname (has a space between two words).
> (5)Now I add new user to the stored database DB:
> Expand Security under DB.
> Rigth click on Users and select new user. Dialog pop up.
> Broswer Login Name: nwe_machine_name\new loginname
> Type in user name: new
> Browser default schema: db_owner
> Select Database Role Menbership: db_owner
> Click OK.
> User new is added in the list.
>
> (6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
> work. When I click the tree node Database Diagrams, I get
> the error massage. I can not either display the diagram or create new
> diagram on the database. The error message:
> "Database diagram support objects cannot be installed because this
> database
> does not have valid owner.
> To continue, first use the Files page of the Database Properties dialog
> box
> ot the Alter Authorization statement
> to set the database owner to a valid login, then add the datatbase diagram
> support objects."
> I can not either show the diagram or create a new diagram on this restored
> database DB.
> I do not know the detail steps for doing it. MSDN articles suggest that
> For compatiable, run
> exec sp_dbcmptlevel 'database_name', '90';
> (It is OK for me to exec. it)
> For alter authorization, run
> ALTER AUTHORIZATION ON DATABASE::databasename to new
> which produced:
> "Msg 15151, Level 16, State 1, Line 1
> Cannot find the principal 'new', because it does not exist or you do not
> have permission."
> I have trouble here. I do not know how to do it. Did I do something wrong?
> What is the correct way/steps?
> Are there any tricks in backup and restore process?
> Thank you for any help.
> David
>
|||Hi David
Could you go to Microsoft SQL Server Management Studio and see under the
Security section of the SQL Server itself (not the database), and see if you
have any of the users in that one correspond/mapped to the database user?
My guess is that if you are only backup and restore the user database, it
will not carry the login information which is stored in system database.
Have you tried to log on as an sa (if it is on mixed mode) and tried to do
the things that you wanted to do?
Lucas
"david" wrote:
[vbcol=seagreen]
> NO, I did not copy user accounts and logins from SQL Server 2000.
> When I made a backup with COMPLETE option, the backup carry all information
> about the database. When I restored the database, all accounts and sttatus
> for old machine automatically appear in the user list for the restored
> database under SQL Server 2005 on new machine.
> David
>
> "bass_player [SBS-MVP]" wrote: