FIX: You cannot create diagrams in SQL Enterprise Manager if you are not a database owner (327145)

SYMPTOMS

In SQL Server Enterprise Manager, if you do not belong to the db_owner database role, you cannot view existing diagrams or create new diagrams. When you try to create a new diagram, you receive the following error message:

"You do not have sufficient privileges to create the new diagram."

After you apply the db_owner database role, you can view existing diagrams and create new diagrams.

CAUSE

When you create a new database in SQL Server 2000 Enterprise Manager, SQL Server does not create any stored procedures or the dtproperties table. SQL Server initializes the dtproperties table the first time you try to create a table or diagram for the new database. SQL Server initializes the stored procedures the first time you try to create a diagram for the new database.

If you want to view existing diagrams or to create new diagrams as a user that is not a dbo, by default, all the stored procedures like dt_adduserobject, dt_getpropertiesbyid or dt_setpropertybyid must have the EXEC permission for the public database role enabled. Additionally, the permissions for the dtproperties table must be set to SELECT, INSERT, UPDATE, DELETE and DRI. However, because these permissions are not set in SQL Server 2000, any attempt to view or to create a new diagram fails.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 How to obtain the latest SQL Server 2000 service pack

WORKAROUND

To work around this behavior, create the permissions manually.

For example:

In SQL Enterprise Manager, move to the affected database.
Grant SELECT, INSERT, UPDATE, DELETE and DRI permissions for the public database role of the dtproperties table.
Grant EXEC permissions for the public database role to all these stored procedures:
dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_whocheckedout
dt_whocheckedout_u

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

This problem was first corrected in SQL Server 2000 Service Pack 1.

NOTE: SQL Server 2000 Service Pack 1, or later, does not change the permission settings for databases that were created in SQL Server 2000 (RTM). For this reason, the problem still occurs in these databases. To work around this issue and avoid the problem, use any of the methods presented in the "Workaround" section.