backup/restore from SQL 2000 to SQL 2005: diagram ownership

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: use Enterprise Manager with COMPLETE backup setup, owners: dbo
and machine name\my login name.
(2) Copy backup file to another machine with SQL Server 2005.
(3) Start SQL Server Management Studio of SQL Server 2005.
(4) Use Restore to restore the backup to a database.
(5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
work.
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 do not know the detail steps for doing it. In the MSDN, it suggests that
For compatiable, run
exec sp_dbcmptlevel 'database_name', '90';
It is OK for exec.
For alter authorization, run
ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
This produces an error: 15151
Are there any tricks in backup and restore process?
Thank you for any help.
David> This produces an error: 15151
Can you show us the error message and not only the error number?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"david" <david@.discussions.microsoft.com> wrote in message
news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
> I have trouble with it. I have tried several ways and talked to some peopl
e
> in the forum, but it is not working. The following is waht I did.
> (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
> and machine name\my login name.
> (2) Copy backup file to another machine with SQL Server 2005.
> (3) Start SQL Server Management Studio of SQL Server 2005.
> (4) Use Restore to restore the backup to a database.
> (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
> work.
> 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 databas
e
> does not have valid owner.
> To continue, first use the Files page of the Database Properties dialog bo
x
> ot the Alter Authorization statement
> to set the database owner to a valid login, then add the datatbase diagram
> support objects."
> I do not know the detail steps for doing it. In the MSDN, it suggests that
> For compatiable, run
> exec sp_dbcmptlevel 'database_name', '90';
> It is OK for exec.
> For alter authorization, run
> ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
> This produces an error: 15151
> Are there any tricks in backup and restore process?
> Thank you for any help.
> David
>
>|||Can not find the principal 'valid_login', because it does not exist.
Should 'valid_login' be a user name?
How to create a correct user name?
I created one, and use the username instead of 'valid_login' and got same
error.
David
"Tibor Karaszi" wrote:

> Can you show us the error message and not only the error number?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
>|||If "valid_login" is a username, how to create such a user?
any detail steps?
Thanks
David
"Tibor Karaszi" wrote:

> Can you show us the error message and not only the error number?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
>|||> Should 'valid_login' be a user name?
No, it should be a login name. If you don't know the difference between a lo
gin and a user, I
strongly suggest you read up on the subject. Here's a good start:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808
659449c5.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"david" <david@.discussions.microsoft.com> wrote in message
news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...[vbcol=seagreen]
> Can not find the principal 'valid_login', because it does not exist.
> Should 'valid_login' be a user name?
> How to create a correct user name?
> I created one, and use the username instead of 'valid_login' and got same
> error.
> David
> "Tibor Karaszi" wrote:
>|||Is a lohin name displayed under security under databases tree node?
David
"Tibor Karaszi" wrote:

> No, it should be a login name. If you don't know the difference between a
login and a user, I
> strongly suggest you read up on the subject. Here's a good start:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-78
08659449c5.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...
>|||No, under the server node (the root node), in the security folder.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"david" <david@.discussions.microsoft.com> wrote in message
news:65951F87-DC9D-4CC9-8819-896924752E32@.microsoft.com...[vbcol=seagreen]
> Is a lohin name displayed under security under databases tree node?
> David
> "Tibor Karaszi" wrote:
>|||Thank you.
so my login name in new machine should be (under sqlserver\security\logins:
new_machine_name\Firstname Lastname (a sapce between them)
So I should use
ALTER AUTHORIZATION on DATABASE::databasename new_machine_name\Firstname
Lastname
Now I get error:
incorrect syntax near 'new_machine_name'.
How to correct it?
David
"Tibor Karaszi" wrote:

> No, under the server node (the root node), in the security folder.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:65951F87-DC9D-4CC9-8819-896924752E32@.microsoft.com...
>|||I use
ALTER AUTHORIZATION on DATABASE::databasename TO new_machine_name\Firstname
Lastname
and got error: Incorrect syntax near ''.
David
"david" wrote:
[vbcol=seagreen]
> Thank you.
> so my login name in new machine should be (under sqlserver\security\logins
:
> new_machine_name\Firstname Lastname (a sapce between them)
> So I should use
> ALTER AUTHORIZATION on DATABASE::databasename new_machine_name\Firstname
> Lastname
> Now I get error:
> incorrect syntax near 'new_machine_name'.
> How to correct it?
> David
> "Tibor Karaszi" wrote:
>|||I used
ALTER AUTHORIZATION on DATABASE::databasename TO 'new_machine_name\Firstname
Lastname'
and I get error:
incorrect syntax near 'new_machine_name\Firstname Lastname' .
If I use
ALTER AUTHORIZATION on DATABASE::databasename TO new_machine_name\Firstname
Lastname
I fet error: incorrect syntax near ''
David
"david" wrote:
[vbcol=seagreen]
> Thank you.
> so my login name in new machine should be (under sqlserver\security\logins
:
> new_machine_name\Firstname Lastname (a sapce between them)
> So I should use
> ALTER AUTHORIZATION on DATABASE::databasename new_machine_name\Firstname
> Lastname
> Now I get error:
> incorrect syntax near 'new_machine_name'.
> How to correct it?
> David
> "Tibor Karaszi" wrote:
>