Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

security does not allow to work on "Database Diagrams" in SS2005

Posted on 2009-07-15
16
Medium Priority
?
337 Views
Last Modified: 2012-05-07
I am trying to work on "Database Diagrams" in SQL Server 2005 and it complains that I should be the owner of the DB to work on the . I right clicked on the database and checked properties, and i do see that I am the owner, but when I go under Filegroups, and see who is the owner; it is blank, and if I try to add me there as the owner, it is not allowing me.

Any thoughts how to over come this security issue.

Thanks
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 3
  • +1
16 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24865471
Have you tried:
sp_changedbowner 'YourUserName'
0
 
LVL 2

Expert Comment

by:kbac
ID: 24867329
If the ownership adjustment as acperkins suggested does not work, check your compatibility level. Right click database--> properties--> options

If you have restored this database from a SQL server 2000 db, you'll need to change the compatibility level to 80 (Sql server 2000) for opening the diagrams.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 24868803
acperkins,
Thanks for the tip, but that did not help. I am the owner of the DB..... just not the files!
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 22

Expert Comment

by:dportas
ID: 24869083
There is no owner for files in SQL Server. Please tell us exactly what error message you are experiencing.
It has to be said that the ability to modify the database from a diagram in SQL Server is a truly terrible feature. Notoriously buggy and reportedly actually dangerous in some cases. I don't know why you want to do this but if you really feel you must then be careful about using it on any production system. I suggest you only do it against a database that doesn't matter - one on your local machine for example.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 24869330
dportas,
I am using the Diagrams in my desktop for some verifications-safe for data.

Here is the place to put the owner for Files (apart from the database). (see attached)
Owner-for-Files.bmp
0
 
LVL 22

Expert Comment

by:dportas
ID: 24869577
That's not the owner of the files. It's the owner of the database. SQL Server has no concept of a file owner.
What error message are you getting that makes you think you have a problem with files?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 24871867
kbac,
It is a SQL Server 2005 database, and the compatibility level is indeed at 90.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 24871889
dportas,
Please take a look at the message, when I try to access 'Database Diagrams'.
error-message.bmp
0
 
LVL 22

Expert Comment

by:dportas
ID: 24872443
My practice is to make SA the owner of every database. This has nothing to do with your individual user permissions and it doesn't expose any special security flaws, it's just that a valid owner must be specifed. I definitely wouldn't recommend using any Windows login as a database owner because if the user's account expires then SQL can't verify it and some things may break as a result.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24875028
Most definitely.

anushahanna,
As I suggested earlier you have a problem with the Owner of the database you need to use sp_changedbowner to set it right.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 24877680
acperkins
I did run the command you recommended, but it did not help. It gave the  message "Command(s) completed successfully."; but not sure what exactly happened, as the error message or the feature of Database Diagram is still not available.

I still get the error message (See id 24871889). And that error message is point to the place in snapshot I posted in ID 24869330.

In this case, the database owner is one with full system admin previledges.

thanks
0
 
LVL 2

Accepted Solution

by:
kbac earned 2000 total points
ID: 24878375
Try this:

EXEC sp_dbcmptlevel 'your database name here', '90';
go

ALTER AUTHORIZATION ON DATABASE::'your database name here' TO 'your valid login name here'
go
use ['your database name here']
go
EXECUTE AS USER = N'dbo' REVERT
go


Or, you can use Visual Studio, and use the data connections to register your db server, and open the db diagrams there. Just like your SQL server management tool, it will ask if you want the required objects to be created, accept this and then try creating db diagrams on the management tool.

And finally, you can still try to change the compatibility level to 80, try, then change it to 90 again and try if those don't work.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 24880861
kbac
When I tried the code, at the 'execute' line, it complains the following:

Could not obtain information about Windows NT group/user , error code 0x6e.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 24880971
never mind.. i had the wrong domain. Thanks kbac
0
 
LVL 2

Expert Comment

by:kbac
ID: 24893092
Oh did it solve it? Gratz.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 24895327
kbac, Yes, it did. thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question