AdventureWorksDB

I can't view the AdventureWorksDB database diagram even after the ALTER
AUTHORIZATION statement.
Thanks..>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
Typically the problem is that the database is not in 90 compatibility level.
You might try verifying the compatibility level of the database (EXEC
sp_dbcmptlevel AdventureWorks)
and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
90; ).
If that doesn't solve the problem, you'll need to provide more details such
as the error message your getting.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
> Thanks..|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> Typically the problem is that the database is not in 90 compatibility level.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details such
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> > Thanks..
>
>|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> Typically the problem is that the database is not in 90 compatibility level.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details such
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> > Thanks..
>
>|||> I am logged in using "sa". Is "sa" valid in 2005?
Yes, sa is valid in 2005. I noticed in the Books Online topic
"Understanding Database Diagram Ownership (Visual Database Tools) ", that it
says the following:
"To use Database Diagram Designer it must first be set up by a member of the
db_owner role (a role of Microsoft SQL Server databases) to control access
to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
as an experiment, please use the ALTER AUTHORIZATION statement and change
the ownership to dbo and try again.
BTW, there is no AdventureWorks diagram that comes with the sample database,
but the support objects mentioned in the error message are required to
create a diagram so they get created when you just click on the Database
Diagram folder if they don't alread exist. I mention this just so you know
that once we get this figured out, there won't be a diagram there anyway
(but you can certainly create one on your own). If what you're really
looking for is an existing diagram of AdventureWorks, you can download an
.html or .vsd version from here:
http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the
> Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set
> the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
> "Gail Erickson [MS]" wrote:
>> >I can't view the AdventureWorksDB database diagram even after the ALTER
>> > AUTHORIZATION statement.
>> Typically the problem is that the database is not in 90 compatibility
>> level.
>> You might try verifying the compatibility level of the database (EXEC
>> sp_dbcmptlevel AdventureWorks)
>> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel
>> AdventureWorks,
>> 90; ).
>> If that doesn't solve the problem, you'll need to provide more details
>> such
>> as the error message your getting.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> "mmc" <mmc@.discussions.microsoft.com> wrote in message
>> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>> >I can't view the AdventureWorksDB database diagram even after the ALTER
>> > AUTHORIZATION statement.
>> > Thanks..
>>|||mmc wrote:
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
The error message is complaining about an invalid database owner. What
user is shown as the owner of the AdventureWorks database?|||Thanks. I'll just download the diagram.
"Gail Erickson [MS]" wrote:
> > I am logged in using "sa". Is "sa" valid in 2005?
> Yes, sa is valid in 2005. I noticed in the Books Online topic
> "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> says the following:
> "To use Database Diagram Designer it must first be set up by a member of the
> db_owner role (a role of Microsoft SQL Server databases) to control access
> to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> as an experiment, please use the ALTER AUTHORIZATION statement and change
> the ownership to dbo and try again.
> BTW, there is no AdventureWorks diagram that comes with the sample database,
> but the support objects mentioned in the error message are required to
> create a diagram so they get created when you just click on the Database
> Diagram folder if they don't alread exist. I mention this just so you know
> that once we get this figured out, there won't be a diagram there anyway
> (but you can certainly create one on your own). If what you're really
> looking for is an existing diagram of AdventureWorks, you can download an
> ..html or .vsd version from here:
> http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...
> > The sp_dbcmptlevel AdventureWorks returned a "90".
> > The error everytime i try to open the diagram:
> > "The database diagram support objects can not be installed because because
> > this database does not have a valid owner. To continue, first use the
> > Files
> > page of the Database Properties dialog box or the ALTER STATEMENT to set
> > the
> > database owner to a valid login, then add add the database diagram support
> > objects".
> > I am logged in using "sa". Is "sa" valid in 2005?
> > Thanks...
> >
> >
> > "Gail Erickson [MS]" wrote:
> >
> >> >I can't view the AdventureWorksDB database diagram even after the ALTER
> >> > AUTHORIZATION statement.
> >>
> >> Typically the problem is that the database is not in 90 compatibility
> >> level.
> >> You might try verifying the compatibility level of the database (EXEC
> >> sp_dbcmptlevel AdventureWorks)
> >> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel
> >> AdventureWorks,
> >> 90; ).
> >>
> >> If that doesn't solve the problem, you'll need to provide more details
> >> such
> >> as the error message your getting.
> >>
> >> --
> >> Gail Erickson [MS]
> >> SQL Server Documentation Team
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights
> >> Download the latest version of Books Online from
> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> >>
> >> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> >> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >> >I can't view the AdventureWorksDB database diagram even after the ALTER
> >> > AUTHORIZATION statement.
> >> > Thanks..
> >>
> >>
> >>
>
>|||mmc <mmc@.discussions.microsoft.com> wrote :
> > The error everytime i try to open the diagram:
> > "The database diagram support objects can not be installed because because
> > this database does not have a valid owner. To continue, first use the Files
> > page of the Database Properties dialog box or the ALTER STATEMENT to set the
> > database owner to a valid login, then add add the database diagram support objects".
Gail Erickson [MS] wrote:
> I noticed in the Books Online topic
> "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> says the following:
> "To use Database Diagram Designer it must first be set up by a member of the
> db_owner role (a role of Microsoft SQL Server databases) to control access
> to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> as an experiment, please use the ALTER AUTHORIZATION statement and change
> the ownership to dbo and try again.
I am able to reproduce the error on SQL Server 2005 Express Edition
SP1, with Management Studio Express SP1. The owner of the database was
a windows login (the windows user that was currently logged-on) which
is a member of the Administrators group. In Security / Logins there is
a login for BUILTIN\Administrators. I followed the instructions
described in the error message to change the database owner to 'sa' and
then the creation of database diagram support objects succeeded.
However, I think it should also work if the database owner is a windows
login (for a user which is member of a windows group that has a SQL
login), because these are the default installation options.
Razvan|||I think you are right about the windows login although I haven't tried it yet
. I am testing this using SQL authentication. It just doesn't make sense to
be able to do almost anything using "sa", but then you have to be in a
windows domain to view the diagram. This is, I guess, one of the subtle
differences between 2000 and 2005.
"Razvan Socol" wrote:
> mmc <mmc@.discussions.microsoft.com> wrote :
> > > The error everytime i try to open the diagram:
> > > "The database diagram support objects can not be installed because because
> > > this database does not have a valid owner. To continue, first use the Files
> > > page of the Database Properties dialog box or the ALTER STATEMENT to set the
> > > database owner to a valid login, then add add the database diagram support objects".
> Gail Erickson [MS] wrote:
> > I noticed in the Books Online topic
> > "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> > says the following:
> > "To use Database Diagram Designer it must first be set up by a member of the
> > db_owner role (a role of Microsoft SQL Server databases) to control access
> > to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> > as an experiment, please use the ALTER AUTHORIZATION statement and change
> > the ownership to dbo and try again.
> I am able to reproduce the error on SQL Server 2005 Express Edition
> SP1, with Management Studio Express SP1. The owner of the database was
> a windows login (the windows user that was currently logged-on) which
> is a member of the Administrators group. In Security / Logins there is
> a login for BUILTIN\Administrators. I followed the instructions
> described in the error message to change the database owner to 'sa' and
> then the creation of database diagram support objects succeeded.
> However, I think it should also work if the database owner is a windows
> login (for a user which is member of a windows group that has a SQL
> login), because these are the default installation options.
> Razvan
>|||mmc wrote:
> It just doesn't make sense to
> be able to do almost anything using "sa", but then you have to be in a
> windows domain to view the diagram.
Currently, it's the opposite situation: if you want to create the
diagramming support objects in AdventureWorks and the database owner is
a windows user, you have to change the owner to 'sa'.
However, I tried the same thing on a newly created database and the
creation of diagramming support objects succeeded, even if the database
owner was the same windows user (as the one which was the owner of
AdventureWorks, when the operation initially failed). It looks like
there was a problem with the way AdventureWorks was installed, because
when I looked at "Database Properties / Files / Owner" for the new
database, the owner was specified; but for AdventureWorks, the Owner
textbox (in the Files page of the Database Properties window) was
blank; however, the owner was shown for AdventureWorks in the General
page of the Database Properties window.
Razvansql