SQL Server – Database Diagram Permissions

Request

Last Friday I received a new request.

Here is the request:

Image

Textual

Hi Daniel,

I created this ticket to get “DB owner” access for the REDWOOD* DBs, I need this access so I can create DB diagrams.
Let me know if you need any additional info.

Docs

Googled to make sure db_owner privileges is indeed needed.

And, confirmed it is needed and so went ahead and granted it.

Listed below is the referenced documentation.

Understanding Database Diagram Ownership (Visual Database Tools)

Here is the referenced work.

Image

Textual

  1. To use Database Diagram Designer it must first be set up by a member of the db_owner role (a role of Microsoft SQL Server databases) to control access to diagrams. Each diagram has one and only one owner, the user who created it.
  2. 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.
  3. 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.

Scenario

Good weekend.

And, with a rested mind wanted to review the steps the user followed and how the system responds without db_owner permission.

 

New Database Diagram

Request to create database diagram

Is NOT db_owner

Here is what happens when the user is not a db_owner.

SSMS

Image

Textual

A member of the db_owner role must use the database diagramming functionality in order to setup the required database diagramming objects on the SQL Server.

 

SQL Server Profiler

Image

Textual
  1. SELECT [DBLAB].dbo.fn_diagramobjects()
    • Error :- Cannot find either column “DBLAB” or the user-defined function or aggregate “DBLAB.dbo.fn_diagramobjects”, or the name is ambiguous.
  2. exec sp_executesql N’SELECT dtb.compatibility_level AS [CompatibilityLevel],
    dtb.name AS [DatabaseName2] FROM master.sys.databases AS dtb WHERE
    (dtb.name=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’DBLAB’
  3. SELECT CONVERT(bit,IS_MEMBER(‘db_owner’))
Explanation
  1. Issue query against dbo.fn_diagramobjects scalar function
  2. Determine database’s compatibility level
  3. If current user is member of db_owner

 

Is db_owner

Here is what happens when the user is a db_owner.

Image

Textual

This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?

 

SQL Server Profiler

Image

Textual
  1. SELECT [DBLAB].dbo.fn_diagramobjects()
    • Error :- Cannot find either column “DBLAB” or the user-defined function or aggregate “DBLAB.dbo.fn_diagramobjects”, or the name is ambiguous.
  2. exec sp_executesql N’SELECT dtb.compatibility_level AS [CompatibilityLevel],
    dtb.name AS [DatabaseName2] FROM master.sys.databases AS dtb WHERE
    (dtb.name=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’DBLAB’
  3. SELECT CONVERT(bit,IS_MEMBER(‘db_owner’))
  4. execute as user = N’dbo’;
    revert;
Explanation
  1. Issue query against dbo.fn_diagramobjects scalar function
  2. Determine database’s compatibility level
  3. If current user is member of db_owner
  4. Execute as dbo
  5. Revert

Objects Created

Once logged in as sysadmin opted to create diagramming objects, here are the objects created and permissions granted.

Details

  1. Create Database Objects
    • Create Stored Procedure ( SP )
      • dbo.sp_upgraddiagrams
      • dbo.sp_helpdiagrams
      • dbo.sp_helpdiagramdefinition
      • dbo.sp_creatediagram
      • dbo.sp_renamediagram
      • dbo.sp_alterdiagram
      • dbo.sp_dropdiagram
    • Create Function
      • dbo.fn_diagramobjects
    • Create Table
      • dbo.sysdiagrams
      • dbo.sysdiagram_properties
  2. Add Extended Property
    • For each object created, please add extended property
  3. Clean Up tasks documented
    • Though obviously not acted upon, the objects that should be removed to rollback the changes are listed

Tabulated

Here is a tabulated view of the objects created.

Object Type Object Name Permission
Procedure
dbo.sp_upgraddiagrams
dbo.sp_helpdiagrams Grant execute to public;
Deny execute to guest;
dbo.sp_helpdiagramdefinition Grant execute to public;
Deny execute to guest;
dbo.sp_creatediagram Grant execute to public;
Deny execute to guest;
dbo.sp_renamediagram Grant execute to public;
Deny execute to guest;
dbo.sp_alterdiagram Grant execute to public;
Deny execute to guest;
dbo.sp_dropdiagram Grant execute to public;
Deny execute to guest;
Function
dbo.fn_diagramobjects Grant execute to public;
Deny execute to guest;
Table
dbo.sysdiagrams
dbo.sysdiagram_properties

 

Summary

Here I am granting db_owner permission to a user so she can diagram databases.

Even worse Opened up a Connect Item.

Thanks goodness for a long weekend.

Wish I could blame it on the smoke, but then things cleared up on Sunday.

All the faults on a lazy Friday.

Could have and should have waited on a Monday and confirmed that once the dba provisioned the targeted database, db_owner is likely not needed.

One thought on “SQL Server – Database Diagram Permissions

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s