question

dosberg avatar image
dosberg asked

Database Diagrams and db_owner

I have a user who is designing his own objects in his own database in Sql 2005. He wants to use the database diagram tool in SSMS. I'd perfer for him not be in db_owner. So far, my testing indicates that the diagrams don't support schemas and as a ddl_admin you can create a table but can't edit it after the first save. Seems the tool is really only for db_owner members. Anyone know of a way the diagrams can be effectively used and not be a member of db_owner?
databasedatabase-diagrams
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wow someone finds those useful? :) Seriously though, Oleg has mentioned my previous answer about this - a different tool is going to better all round
1 Like 1 ·
Oleg avatar image
Oleg answered
I doubt that this is possible. Whenever the database diagram is saved, it needs to apply the changes (whatever they are) to the underlying objects immediately, so it would not make much sense to allow lesser (than a member of db_owner role) mortals to use the diagrams. The [answer to this question][1] by Kevan Riley seems to confirm that. Maybe using a different tool, which would allow designing the diagrams without actually touching the database objects would be a better option in this case. Oleg [1]: http://ask.sqlservercentral.com/questions/6070/permissions-on-sql-server-2005-database-diagrams
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
Why not have him be db_owner on a test/dev server where he cant do any harm, or use a restored copy of the database for him to do the diagrams in? He neednt be db_owner for all databases. What are you going to use the diagrams for? If its for documentation purposes then a different tool might be just as good - as @Oleg suggests. Have you tried anything like SQLDoc ( http://www.red-gate.com/products/sql-development/sql-doc/ ) from RedGate?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.