Share via

A DDL Admin user is not able create/alter/delete tables from inside a database designer diagram in SQL Server 2005

  • Article

If you create a DDLAdmin user (a member of the Db_ddladmin database role), still she will not be able to make any changes to an existing database diagram created by the dbo user.
Despite the DDLAdmin user having all rights to add/alter/delete tables from TSQL script/or SSMS, she cannot do the same from within database diagram.
If a DDLAdmin creates a new table using db diagram, it is automatically marked as (read-only).

Found that when we load the diagram, we check the ALTER, VIEW DEFINITION and CONTROL permissions on all the tables included in that db diagram for that particular user. If it is the DDLAdmin user, he has (by-default) only ALTER permission. This according to the Diagram designer, is not sufficient to allow that user to delete/modify that table. Thus the "delete table from database" option gets grayed out.

* Resolution here is to grant DDLAdmin user CONTROL permission on DBO schema.
* This can be done using following command in that database.

GRANT CONTROL on SCHEMA::<DBO>
TO <DDLAdmin user>

* After doing this, DDLAdmin will be able to see the tables inside the diagram to be NOT marked with read-only and then will be able to add/remove the tables from the diagram successfully.

Comments