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

Article translations Article translations
Article ID: 327145 - View products that this article applies to.
This article was previously published under Q327145
Bug #: 236377 (SQL Server 8.0)
Expand all | Collapse all

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.

Properties

Article ID: 327145 - Last Review: March 4, 2005 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbpending kbsqlserv2000sp1fix KB327145

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com