security does not allow to work on "Database Diagrams" in SS2005
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
Any thoughts how to over come this security issue.
Thanks
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.
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.
ASKER
acperkins,
Thanks for the tip, but that did not help. I am the owner of the DB..... just not the files!
Thanks for the tip, but that did not help. I am the owner of the DB..... just not the files!
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.
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.
ASKER
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
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
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?
What error message are you getting that makes you think you have a problem with files?
ASKER
kbac,
It is a SQL Server 2005 database, and the compatibility level is indeed at 90.
It is a SQL Server 2005 database, and the compatibility level is indeed at 90.
ASKER
dportas,
Please take a look at the message, when I try to access 'Database Diagrams'.
error-message.bmp
Please take a look at the message, when I try to access 'Database Diagrams'.
error-message.bmp
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.
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
never mind.. i had the wrong domain. Thanks kbac
Oh did it solve it? Gratz.
ASKER
kbac, Yes, it did. thanks.
sp_changedbowner 'YourUserName'