locked
Can't Create Database Diagrams RRS feed

  • Question

  • In SQL 2008, Is it a way to grant permission to developer for view and create database diagrams without giving them dbo permission?
    Meng Chew
    Tuesday, November 16, 2010 10:42 AM

Answers

  • Hi Meng,

    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,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, November 17, 2010 3:24 AM

All replies

  • Of course, you meant db_owner role. - RLF
    Tuesday, November 16, 2010 3:21 PM
  • Hi Meng,

    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,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, November 17, 2010 3:24 AM