6

Could you please advise, what are permissions required to give user access to read Database diagrams in SQL Server 2005?

Thanks a lot!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Budda
  • 18,015
  • 33
  • 124
  • 206

3 Answers3

9

From BOL

  • Although any user with access to a database can create a diagram, once the diagram has been created, the only users who can see it are the diagram's creator and any member of the db_owner role.
  • Ownership of diagrams can only be transferred to members of the db_owner role. This is only possible if the previous owner of the diagram has been removed from the database.
  • If the owner of a diagram has been removed from the database, the diagram will remain in the database until a member of the db_owner role attempts to open it. At that point the db_owner member can choose to take over ownership of the diagram.

So, db_owner/dbo is best

gbn
  • 422,506
  • 82
  • 585
  • 676
8

Try with this:

ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO sa;
pcofre
  • 3,976
  • 18
  • 27
  • 2
    Can you explain why this would help please? – gbn Jan 11 '11 at 19:02
  • Question was what permission are needed for user to create diagram, and not which user has permissions to create diagram. Logically, `sa` user is `sysadmin` and has full permissions. – veljasije Sep 11 '13 at 08:05
0

I've just found this in MSDN:

... 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

less than db_owner/dbo

sead4711
  • 29
  • 1
  • 1
  • 10