Microsoft KB Archive/327145

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

Article ID: 327145

Article Last Modified on 3/4/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q327145



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:

&quot;You do not have sufficient privileges to create the new diagram.&quot;

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 &quot;Applies to&quot; 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 &quot;Workaround&quot; section.

Additional query words: security

Keywords: kbbug kbsqlserv2000sp1fix kbpending KB327145

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.