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/pr...oads/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:

> Typically the problem is that the database is not in 90 compatibility leve
l.
> 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 suc
h
> 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/pr...oads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.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:

> Typically the problem is that the database is not in 90 compatibility leve
l.
> 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 suc
h
> 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/pr...oads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>
>|||> 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/...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/pr...oads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...[vbcol=seagreen]
> 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/pr...oads/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:

> Typically the problem is that the database is not in 90 compatibility leve
l.
> 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 suc
h
> 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/pr...oads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.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:

> Typically the problem is that the database is not in 90 compatibility leve
l.
> 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 suc
h
> 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/pr...oads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>
>|||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 File
s
> page of the Database Properties dialog box or the ALTER STATEMENT to set t
he
> 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?|||> 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/...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/pr...oads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...[vbcol=seagreen]
> 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:
>

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

AdventureWorks DB question

Hi, I have been looking at the Adventure works DB diagram and I am confused about the relationship that employees, customers and individuals has with contact i.e. a 1-1, 1-many or many-many relationship. Also why is it broken down like that.

Thanks,

Nadim.

Hi Nadim,

AdventureWorksDB is designed to show a complex data schema in a nomalized structure. AdventureWorks is probably a bit overcomplex if you're just learning about normalization, but once you understand the concepts, it becomes more clear.

Note: If you are new to normalization, you should search for a few topics about it to get the basics. You might also find the Northwind or Pubs sample databases to be a bit more understandable. You can download either from http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en. They are SQL 2000 samples, but they will still work in 2005.

On to the description...

If you look at the fields in each of the tables, you'll see that the Contact table contains the base information about a person, e.g. first name, last name, phone, etc. The Contact table is linked to both the Employee and Individual tables. Both Employees and Individuals are types of people that have special properties in addition to the base person information stored in the Contact table. So an Employee has a ManagerID and LoginID, but an Individual doesn't. Because of the relationship between Contact and Employee, there is no need to store the first name, last name, etc. in the Employee table, it is retrieved through a join.

If I wanted to get a list of Employee names, with Titles and Hire Dates, I'd need to write a query that uses both the Contact and Employee table, as follows:

SELECT C.FirstName, C.LastName, E.Title, E.HireDate
FROM Person.Contact C INNER JOIN HumanResources.Employee E
ON C.ContactID = E.ContactID

The relationship with Individual is similar, only it extends through Individual onto Customer. Each Customer has an Individual associated with it, and that Individual, is a person who has Contact information. I'll be honest, I don't really know why they split Customer and Individual, it could have something to do with the way they wanted to use the data being stored.

Hopefully that brings a little light to the subject.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

AdventureWorks BI

Hi, I was looking for documentation of AdvertureWorks BI (if a diagram exists, other kind of documentation) if anyone can help pls post. I already have a diagram in Visio but I need more documentation for the BI one.

Thanks.

Hi Aldo,

I don't know of any detailed documentation on the Analysis Services databases, though I agree this is something we need to get out there. If you have a specific question, feel free to ask.

Thanks,

Rob Zare

|||

Download the latest version of Books Online: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Then paste this in the "URL" bar: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/advwks9/html/4c62907b-c882-48ff-b92c-b82ccfd7e06f.htm

Buck Woody

http://www.buckwoody.com

Advantages of Database Diagram

What is the advantages/disadvantages of using Database Diagram and link all the tables in MS SQL Server Management Studio versus letting the application check and link the different tables at run time? Currently, I do not have all my tables linked in a Database Diagram. I do everything at run time in my application code behind. What are the best practices? Which is easier or perhaps more secure?

I always used the diagram tool in SQL2000 - not only is it a great resource for new DBA's and developers, it adds constraints that you might otherwise forget. It's a great simple way to ensure you don't end up with missing links in your data.

|||

Many thanks for the response. Is constraint the biggest advantage of using diagram? By the way, can I use MS SQL Server Management Studio 2005 to diagram a database tables on SQL Server 2000?

|||

Lack of referential integrity can lead to very hard to trace faults. Where relationships exist, it is far better to define then at the database level as the relationship is then always enforced.

You can have more than one diagram. It is not necessary to have diagrams to define foreign key relationships, it is just a bit easier that way.

|||

Thanks for the responses.

sql