SQL Server Management Studio Display Database Diag

问题:

I was wondering if anybody knew exactly what permissions where needed on a database in SQL Server 2005+ so that when a person uses SQL Server Management Studio, they could then be able to at minimum see the Database Diagrams.

I have tried giving the person db_datareader, db_datawriter, and db_ddladmin, but to no avail.

I have also tried giving them access in the Properties → Effective Permissions of the user. Under Effective Permissions, I could not find the database object type for "database diagrams" or anything like that to give the user access to.

They are running SQL Server Management Studio (non-express version.)

Any help would be great.

FYI, I did not want to give them db_owner access.

EDIT:

1. As to one of the comments: Yes, the database is an SQL Server 2005 database.
2. As to one of the answers, moving the DB from production to development is not an option.

回答1:

Giving admin rights is not the right approach, you need to be Database Owner for Database Diagrams, check out this thread for more details.

回答2:

First you need to set up Diagram Designer (you need to be db_owner for that). Just expand the Diagrams node, and press 'Yes' to enable diagramming.

After that all other db users can create diagrams and see their own diagrams. Only db_owner can see other's diagrams.

Also the db_owner can change diagram owner to himself, but the original owner must be removed from database before doing that.

回答3:

Copy the database to a development system, and grant the developers administrative rights. Anything else is a waste of time (like researching this question.)

See this post for better explanations.