I am unable to use the SQL Server database diagram functionality

Question

I am unable to access the SQL Server database diagramming functionality. I get an error:

A member of db_owner must use the database diagramming functionality

When I contacted my service provider they said that they did not grant me the db_owner role and I need to upgrade to VPS-server. I tried to find my permission using a script and I got the following: https://prnt.sc/m1swe9

Is there any workaround for the issue, can I request for a less privileged role?

Answer 1

To use Database Diagram Designer it must first be set up by a member of the db_owner role (a role of SQL Server databases) to control access to diagrams.

Some points to keep in mind about diagram ownership:

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

Please refer to the similar thread, Aaron gave two workarounds:

1. In a Logon trigger, update the principal_id of all diagrams to be the current login. This means they will have access to all diagrams until the next person logs in.
2. Use a trigger on the sysdiagrams table itself, and whenever a diagram is created or updated, add / update a copy for each principal (with their user name appended).

Thread link: https://dba.stackexchange.com/questions/43946/permission-required-to-view-a-database-diagram