472,145 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,145 software developers and data experts.

Want to be able to create or view database diagrams

Our MS SQL (SQL Server 2000) DBA has database privileges locked down
pretty tightly. We end users/developers do not have administrator
privileges for most databases. That arrangement has worked out OK for
the most part. However, it's a bit aggravating that we can't even
create our own database diagrams. When we attempt to do so (in
Enterprise Manager), we get a dialog that says "You do not have
sufficient privilege to create a new database diagram."

Our DBA is so busy that it's difficult to get them to create ones for
us. And even when they do, it seems that we can't even view it online
-- we rely on the DBA to give us printed copies or screenshots for our
documentation. How ridiculous is that? We'd also like to be able to
manipulate the diagrams online so that we can more easily study our
table structures, indexes, foreign key constraints, etc. In fact,
there doesn't even seem to be any other way to easily view current
foreign key constraints.

I realize that this might have to do with the diagramming tool having
the ability to actually manipulate the database in ways we aren't
supposed to do (e.g., like creating new tables). However, isn't there
a "read-only" version of this tool that will give us what I'm looking
for? Short of that, are there any other free or cheap tools that will
provide this? Thanks!

(Please forgive my ignorance if this has been hashed out before.
However, I did search news groups and the web before posting, figuring
this had been covered before, but I didn't find anything that
specifically addressed this issue.)

Jan 20 '06 #1
3 10510
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).


Jan 21 '06 #2
Razvan -- Thank you so much for your thourough and informative reply.
I ran the "EXEC sp_helpuser" command for the user account in question,
and it appears that it already has db_datareader and db_datawriter
roles assigned -- those were in fact the only two roles returned by
this command. Were those the only two roles you had assigned when you
tested this?

I'm afraid that our DBA probably won't assign roles other than those
two for this user account. They don't want to give us the ability to
create foreign key constraints, or anything structural -- we can only
manipulate the data. And we're basically OK with that, but we'd still
like to create or at least view diagrams. :-( Any other ideas?

Jan 21 '06 #3
Actually, I just figured out that Visio (for which everyone here has
licenses) has some pretty decent database reverse engineering features.
I was able to use it to develop my own database diagram that is pretty
much the same as what Enterprise Manager's diagramming tool provides.
While it might still be nice to have access to EM's tool, it ooks like
Visio will essentially satisfy my needs. Case closed.

Jan 21 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Saiars | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.