Geek Answers Handbook

SQL Server Management Studio Displaying Database Diagrams (ER) Permissions

I was wondering if anyone knows exactly what permissions are needed in the database in SQL Server 2005+, so when a person uses SQL Server Management Studio, they could at least see the database diagrams.

I tried to provide the person with db_datareader, db_datawriter and db_ddladmin, but to no avail.

I also tried giving them access in properties -> Effective user permissions. In the "Effective Permissions" section, I could not find the type of database object for the "database diagrams" or something like that to give the user access.

They run SQL Server Management Studio (not express version.)

Any help would be great.

FYI, I didn’t want to give them access to db_owner.

EDIT:

  • Regarding one of the comments: Yes, the database is a SQL Server 2005 database.
  • As for one answer, moving the database from production to development is not an option.
+4
source share
3 answers

Giving administrator rights is not the right approach, you need to be the owner of the database for database diagrams, see this topic for more details;

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/8715f383-3e28-4910-93f9-5cca51d48380/

+7
source

First you need to configure the Diagram Designer (for this you need to be db_owner). Just expand the node diagrams and click Yes to enable the diagram.

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

Also, db_owner can change the owner of the chart itself, but the original owner must be removed from the database before doing this.

+5
source

Copy the database to the development system and give developers administrative rights. Everything else is a waste of time (for example, research on this issue).

See this post for more details.

-5
source

More articles:


All Articles