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?
Wow someone finds those useful? :) Seriously though, Oleg has mentioned my previous answer about this - a different tool is going to better all round
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
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?
