Post reply

Grant View Privileges on Database Diagrams (is it possible)

  • February 1, 2013 at 11:40 am

    #394089

    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!

  • February 4, 2013 at 7:17 am

    #1583940

    I'll be getting an upgrade soon. thanks!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply