Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grant View Privileges on Database Diagrams (is it possible) Expand / Collapse
Author
Message
Posted Friday, February 01, 2013 11:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 29, 2013 9:29 AM
Points: 289, Visits: 302
I'm an intern and my boss asked me to look what permission he will need to set to give me the ability to view the Database diagrams he created in two databases. I've found seemingly conflicting information. Some say it is impossible and some see the give a solution. So I don't know what to think.

Here are some things I found. Since I don't have admin privileges, I can't test these out.

To grant 'view diagrams' permissions to a role or login , grant execute permissions on

dt_getobjwithprop_u
dt_getpropertiesbyid_u

This will allow viewing of the diagrams only with no update ability.



http://www.sqlservercentral.com/Forums/Topic326833-23-1.aspx
You can't and for good reason... saving a database diagram will change the database, as well. You certainly don't want the users playing around with your diagram and hitting "Save"...
Diagrams were meant to help design databases... they were never meant as general purpose documentation diagrams open to the public.



http://www.sqlservercentral.com/Forums/Topic497247-359-1.aspx
I don't blame you for not wanting to make developers members of db_owner, but I haven't found any way to make the database diagrams shareable. The only choices seem to be to use a separate diagramming tool or have each developer that needs to see a diagram generate his own in the database.

Greg


Here is something I found at the following website
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/de6cb842-ecac-4c6e-b5b7-85ae62edbc0e/

• Firstly, to user Database Diagram Designer in the Management Studio or Visual Studio, it must first be enabled by a member of the db_owner role to create one or more support objects (table(s), function(s), and stored procedures). For more information see How to: Set Up Database Diagram Designer (http://msdn.microsoft.com/en-us/library/ms189279(v=SQL.100).aspx).

Secondly, after you set up the Database Diagram Designer, you can grant execute premission on sp_creatediagram stored procedure to someone who needs to create the diagram; you can grant execute alter, rename or drop permisson as well. However, please note that you still need related premission on tables, schemas otherwise you may not be able to save the diagram. For instance, if you create a new table in the diagram, you need CREATE TABLE permission on corresponding schema.


Thanks!
Post #1414779
Posted Monday, February 04, 2013 7:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 29, 2013 9:29 AM
Points: 289, Visits: 302
I'll be getting an upgrade soon. thanks!
Post #1415286
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse