Wednesday, March 7, 2012

database diagram

Hi,
I restored my database from the backup file, which is working fine but it
is not showing the database diagram in SQL server management studio. What do
I need to do for that?
Thanks.
Manj.Hi Manj,
I understand that after you restored your database, you found that the
database diagram disappeared in SQL Server Management Studio.
If I have misunderstood, please let me know.
Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
If so, it is by design that the database diagram will not be displayed in
SQL Server 2005 due to structure incompatibility. If your database is SQL
Server 2005, the database diagram should be there in your restored
database.
Anyway for this issue, after you restore your database, you can manually
create a diagram and add all of your tables to it.
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Charles,
Thanks for the reply. It is SQL Server 2005 database. When I click on
database diagram I get the following message:
TITLE: Microsoft SQL Server Management Studio
--
Database diagram support objects cannot be installed 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 AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.
--
BUTTONS:
OK
--
I added the owner from the Files page but still getting the same message.
When I connect to the database it does show the owner in the 'owner box'.
Cheers.
Manj.
"Charles Wang[MSFT]" wrote:
> Hi Manj,
> I understand that after you restored your database, you found that the
> database diagram disappeared in SQL Server Management Studio.
> If I have misunderstood, please let me know.
> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
> If so, it is by design that the database diagram will not be displayed in
> SQL Server 2005 due to structure incompatibility. If your database is SQL
> Server 2005, the database diagram should be there in your restored
> database.
> Anyway for this issue, after you restore your database, you can manually
> create a diagram and add all of your tables to it.
> Please feel free to let me know if you have any other questions or
> concerns. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>
>|||Hi Manj,
This is a known issue that was fixed in SP1 or SP2. When you upgrade a
database from SQL Server 2000 to 2005, the database remains in 80
compatibility mode. To use the database diagram tool in SQL Server 2005,
the database must be set to 90 mode. See this Books Online topic:
http://msdn2.microsoft.com/en-us/library/ms186345.aspx
--
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://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Manjree Garg" <garg@.newsgroup.nospam> wrote in message
news:7CAFD23C-FA12-4766-A26D-10E18863090A@.microsoft.com...
> Hi Charles,
> Thanks for the reply. It is SQL Server 2005 database. When I click on
> database diagram I get the following message:
> TITLE: Microsoft SQL Server Management Studio
> --
> Database diagram support objects cannot be installed 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 AUTHORIZATION statement to set
> the database owner to a valid login, then add the database diagram support
> objects.
> --
> BUTTONS:
> OK
> --
> I added the owner from the Files page but still getting the same message.
> When I connect to the database it does show the owner in the 'owner box'.
> Cheers.
> Manj.
> "Charles Wang[MSFT]" wrote:
>> Hi Manj,
>> I understand that after you restored your database, you found that the
>> database diagram disappeared in SQL Server Management Studio.
>> If I have misunderstood, please let me know.
>> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
>> If so, it is by design that the database diagram will not be displayed in
>> SQL Server 2005 due to structure incompatibility. If your database is SQL
>> Server 2005, the database diagram should be there in your restored
>> database.
>> Anyway for this issue, after you restore your database, you can manually
>> create a diagram and add all of your tables to it.
>> Please feel free to let me know if you have any other questions or
>> concerns. Have a nice day!
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> =====================================================>> When responding to posts, please "Reply to Group" via
>> your newsreader so that others may learn and benefit
>> from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> ======================================================>>
>>
>>|||Hi Gail,
Thanks for the suggestion. Resolved the issue.
Manj.
"Gail Erickson [MS]" wrote:
> Hi Manj,
> This is a known issue that was fixed in SP1 or SP2. When you upgrade a
> database from SQL Server 2000 to 2005, the database remains in 80
> compatibility mode. To use the database diagram tool in SQL Server 2005,
> the database must be set to 90 mode. See this Books Online topic:
> http://msdn2.microsoft.com/en-us/library/ms186345.aspx
> --
> 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://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Manjree Garg" <garg@.newsgroup.nospam> wrote in message
> news:7CAFD23C-FA12-4766-A26D-10E18863090A@.microsoft.com...
> > Hi Charles,
> >
> > Thanks for the reply. It is SQL Server 2005 database. When I click on
> > database diagram I get the following message:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > --
> >
> > Database diagram support objects cannot be installed 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 AUTHORIZATION statement to set
> > the database owner to a valid login, then add the database diagram support
> > objects.
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >
> > I added the owner from the Files page but still getting the same message.
> > When I connect to the database it does show the owner in the 'owner box'.
> >
> > Cheers.
> >
> > Manj.
> > "Charles Wang[MSFT]" wrote:
> >
> >> Hi Manj,
> >> I understand that after you restored your database, you found that the
> >> database diagram disappeared in SQL Server Management Studio.
> >> If I have misunderstood, please let me know.
> >>
> >> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
> >> If so, it is by design that the database diagram will not be displayed in
> >> SQL Server 2005 due to structure incompatibility. If your database is SQL
> >> Server 2005, the database diagram should be there in your restored
> >> database.
> >>
> >> Anyway for this issue, after you restore your database, you can manually
> >> create a diagram and add all of your tables to it.
> >>
> >> Please feel free to let me know if you have any other questions or
> >> concerns. Have a nice day!
> >>
> >> Best regards,
> >> Charles Wang
> >> Microsoft Online Community Support
> >> =====================================================> >> When responding to posts, please "Reply to Group" via
> >> your newsreader so that others may learn and benefit
> >> from this issue.
> >> ======================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> ======================================================> >>
> >>
> >>
> >>
> >>
> >>
>
>|||Hi all,
I have the same problem (SS2005):
TITLE: Microsoft SQL Server Management Studio
--
Database diagram support objects cannot be installed 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 AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.
With:
SELECT USER
it returns "dbo"
Where the problem?
Thanks a lot.
Luigi|||The problem seems to be that the SID for your dbo user inside the database doesn't exist as a login
in the master database. Use either ALTER AUTHORIZATION (as suggested by the error message) or
sp_changedbowner to make sure that the database has an owner that really exist in the master
database (as a login).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Luigi" <ciupazNoSpamGrazie@.inwind.it> wrote in message
news:D544D243-9D98-4432-B91B-9D68BE0C189E@.microsoft.com...
> Hi all,
> I have the same problem (SS2005):
> TITLE: Microsoft SQL Server Management Studio
> --
> Database diagram support objects cannot be installed 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 AUTHORIZATION statement to set
> the database owner to a valid login, then add the database diagram support
> objects.
> With:
> SELECT USER
> it returns "dbo"
> Where the problem?
> Thanks a lot.
> Luigi
>
>|||"Tibor Karaszi" wrote:
> The problem seems to be that the SID for your dbo user inside the database doesn't exist as a login
> in the master database. Use either ALTER AUTHORIZATION (as suggested by the error message) or
> sp_changedbowner to make sure that the database has an owner that really exist in the master
> database (as a login).
Thank you Tibor, I'll make in this way.
Luigi

No comments:

Post a Comment