Posted by Razvan Socol on 01/21/06 09:14

I was able to create a diagram using Enterprise Manager for SQL Server
2000 SP4, when logged as a user which has db_datareader and
db_datawriter roles. I got the following warning at start:
"You are not logged on as the database owner or system administrator.
You might not be able to save changes to tables that you do not own.
Certain edits require CREATE TABLE permission".

As long as the relationships (foreign keys) between the tables were
already created, I was able to arange the tables and save the diagram
without any problem. If I added two tables that are related using a
foreign key, the line between the tables appeared. If you want to see
what foreign keys are created for a table, right click on the table and
choose Relationships. You can also see this way the indexes and the
check constraints defined on the table.

However, if I want to draw a new relationship (even if I uncheck
"Enforce relationship"), this would require creating a foreign key, so
I would need permissions to ALTER TABLE. This permissions are granted
to: the table owner, members of the sysadmin fixed server role, and the
db_owner and db_ddladmin fixed database roles.

My suggestion is to ask the DBA to make you member of the db_ddladmin
role, if you have the responsability of creating foreign keys in the
database. This will allow you to create, alter or drop any object in
the database (but you will not be able to grant any priviledges to
other users).

If you are not allowed to create/modify the foreign keys, you can use
the diagramming tools only as long as you are satisfied with the
relationships (foreign keys) that are already created. If you want to
draw new relationships between the tables (without creating foreign
keys for them), you would have to use another tool (I will let others
respond about which tools are suitable for this purpose).

Razvan

[Back to original message]