You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q327145
This article has been archived. It is offered "as is" and will no longer be updated.
Bug #: 236377 (SQL Server 8.0)
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:
  1. In SQL Enterprise Manager, move to the affected database.
  2. Grant SELECT, INSERT, UPDATE, DELETE and DRI permissions for the public database role of the dtproperties table.
  3. 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.
security
Properties

Article ID: 327145 - Last Review: 12/07/2015 12:06:48 - Revision: 3.2

Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kbpending kbsqlserv2000sp1fix KB327145
Feedback