Non-dbo users may not be able to create Dabase Diagrams in SQL Server 2005

  • Article

When non-dbo users opens a Dabase Diagram in SQL Server 2005, they get message:
---------------------------
Microsoft SQL Server Management Studio
---------------------------
You are not logged on as the database owner or system administrator. You might not be able to save changes to tables that you do not own.
---------------------------
OK   Help  
---------------------------

This behavior is documented in SQL BOL topic "Understanding Database Diagram Ownership (Visual Database Tools)" - https://msdn2.microsoft.com/en-us/library/ms186345.aspx

If you find this restriction very strict, since you want your regular database users (non-dbo) to be able to create db diagrams but still does not want to give a DB Owner rights, read on:

Resoluton here is to create a regular user in a separate schema and make him the owner of that schema. After this, the user will be able to create/alter the diagram designer to add/modify/remove the tables in that schema.