473,692 Members | 2,552 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 10684
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: yensao | last post by:
Hi, I have a hard time to understand difference and similarities between Relational database model and the Object-Oriented model. Can somebody help me with this? Thank you in advance. Yensao
by: Kavita | last post by:
Hello All I am using SQL server 2000 as the backend of my application but don't want my clients tobe able to view or edit the database tables, stored procedures , view etc using enterprise manager or query analyser (or similar tools)How can this be done ? I searched a lot for this but unable to get the correct answer is there anybody to give full solution regarding this. Please relpy me as early as possible
by: ALthePal | last post by:
Hi, I'm not sure if we are able to or even how to loop through the web forms in a VB.NET project during design time. In MSAccess we are able to go through the database -> forms collection and loop through all the forms in a database and pull information about the form (controls and properties). We would need to do the same in our VB.NET project; loop through the project and get the web form's control and property information...
by: craig dunn | last post by:
I've used the code from the post for a while now... but recently upgraded to SQL Server 2005. I've written a new script for SQL Server 2005, which can be found at http://www.codeproject.com/useritems/ScriptDiagram2005.asp if anyone is looking to script out database diagrams... Thanks to the original poster - Clay Beatty - for his effort. URL: ...
by: serge | last post by:
I first got this map from SQL Server Magazine subscription. Now i see it's available to download as a PDF from Microsoft. http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en I've just installed Visual Studio Team Edition for Database Professionals and for the next coming weeks i'll get a chance to see what kind of data
by: johnsocb | last post by:
Pretty new to SQL, but I have had the good luck to be responsible for documenting a very sophisticated program that is a .net application. Anyhow the SQL side of the app is NOT documented with the database diagrams in SQL enterprise manager or with anything else for that matter. I am not sure how this person setup the relationships/ primary key / foreign keys for these tables , but they appear to have none. How does one untangle the system...
by: gvijayasurya | last post by:
11. What are the different tables present in mysql, which type of table is generated when we are creating a table in the following syntax: create table employee(eno int(2),ename varchar(10)) ? 12. Functions in IMAP, POP3 AND LDAP? 13. How can I execute a php script using command line? 14. Suppose your Zend engine supports the mode Then how can u configure your php Zend engine to support mode ? 15. Shopping cart online validation...
by: vicky | last post by:
Hi All, Can u please suggest me some books for relational database design or database modelling(Knowledgeable yet simple) i.e. from which we could learn database relationships(one to many,many to one etc.....),building ER diagrams,proper usage of ER diagrams in our database(Primary key foreign key relations),designing small modules,relating tables and everything that relates about database design....Coz I think database design is the...
by: mca47378 | last post by:
Hi, I attached my database on different machine. Everything seems to be okay as of now, but I dont see my database diagrams here, which I had created and saved on the previous machine in this database. Can anyone please suggest me cuase. Thank you, Mithalesh
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.