This article was previously published under Q327145
Bug #: 236377 (SQL Server 8.0)
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.
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.
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
To work around this behavior, create the permissions manually.
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:
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.