How to: ALTER AUTHORIZATION ON DATABASE

[RaeK]

Boy, they sure don't make learning this SQL server stuff easy with these downloads! Finally got the pubs attached, but got errors when I tried to do the walkthrough program.

So from within the SQL server mgt. studio express, I tried clicking on the database diagram and got an error message regarding authentication. (I had attached the pubs database to my SQL server.) AFter doing some research, found the following article.

Under this article it warns that you may get an error if you attached your database:

http://msdn2.microsoft.com/en-us/library/ms189279.aspx
<start quote>
Important:
The installation of these support objects can fail on a database that has been attached or restored from another instance of SQL Server. This may occur when the database owner name (stored in the database) is not a valid login in the instance of SQL Server the database is being attached or restored to.
Use the following Transact-SQL expression to change the database owner to a valid login in the instance of SQL Server. Then retry the database diagram operation.

ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login

<end of quote>
Where do I go to do this? Which program do I open and which part of it do I open to change this authorization on database. Also, I don't know what my valid login is either. (I'm the only one using my computer.)

Can someone clue me in how to do this?

Thanks
Rae

[Sayed]

Do two things

1. If you have embadded the database, you will have change compatability to sql server 2005 (90). go to properties of database -> option -> compatibilty level.

If this does not work


then try running this code on the same db you want to view diagram for

EXEC sp_dbcmptlevel 'AdventureWorks', '90';

ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO sqayyumi

replace AdventureWorks with the name of db you want the diagram for and sqayyumi with the name of you id you have login to sql server with.

you can see the name of user just ahead of youe sql server name.

[goingolfn99]

Select the database, right click it, select PROPERTIES. A pop up window opens up and in the upper left corner you'll see "SELECT A PAGE". select FILE. Now in the main section on the top you'll see OWNER. Select the button next to the empty box. another pop up box opens up, select browse and then you'll have to select (NT AUTHORITY\SYSTEM). that should do it and then you'll be able to see the DATABASE DIAGRAM.

HOPE THIS HELPS.

KRIS

[BornCoder]

Thanx a lot !!! it helped me too

[silvio_st3]

Thanks goingolfn99.
Mas sencillo que la ayuda MSDN. you help is easier than MSDN.
Gracias.

[adboy77]

Select the database, right click it, select PROPERTIES. A pop up window opens up and in the upper left corner you'll see "SELECT A PAGE". select FILE. Now in the main section on the top you'll see OWNER. Select the button next to the empty box. another pop up box opens up, select browse and then you'll have to select (NT AUTHORITY\SYSTEM). that should do it and then you'll be able to see the DATABASE DIAGRAM.

HOPE THIS HELPS.

KRIS

hey kris, thank for the help it really amazing