Viewing database diagrams

  • richardmgreen1

    SSCrazy Eights

    Points: 9949

    Hi all

     

    We've designed some database diagrams on our DEV server (we have SA access).

    We want our analysts to be able to see these diagrams but we don't want them to have db_owner access (even though it's a DEV server).

    What I've done so far it to create a role within the database and assigned it EXECUTE permissions on the following stored procedures (which I found by Googling):-

    sp_alterdiagram

    sp_creatediagram

    sp_dropdiagram

    sp_helpdiagramdefinition

    sp_helpdiagrams

    sp_renamediagram

    sp_upgradediagrams

     

     

    Unfortunately, they still can't see the diagrams.

     

    Anyone any ideas on how to allow users to see all diagrams without having db_owner access?

     

    TIA

     

    Richard

  • Eric M Russell

    SSC Guru

    Points: 125100

    It is frustrating that the diagramming tool built into SSMS hasn't evolved in any significant way since 2005.

    One thing you can do is print to diagrams to PDF documents or use the Copy Diagram to Clipboard option to paste diagram image into something like MS Word.

    Also, maybe restore a copy of the database and then granting analysts db_owner access there.

     

     

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Sue_H

    SSC Guru

    Points: 90750

    Eric M Russell wrote:

    It is frustrating that the diagramming tool built into SSMS hasn't evolved in any significant way since 2005. One thing you can do is print to diagrams to PDF documents or use the Copy Diagram to Clipboard option to paste diagram image into something like MS Word. Also, maybe restore a copy of the database and then granting analysts db_owner access there.    

     

    They figured out how to address it - database diagrams no longer available starting with 18.0 preview RC1. Maybe that will change but check the deprecated features list:

    https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-2017

    Sue

     

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720940

  • richardmgreen1

    SSCrazy Eights

    Points: 9949

    So there's no set of permissions I can give just to allow users to access the database diagrams.  Oh well.

     

    Looks like we'll be exporting them and sticking them to the wall once they've been printed.

  • Eric M Russell

    SSC Guru

    Points: 125100

    I'm surprised that Microsoft hasn't invested in the development or acquisition of a good basic ERD tool over the years. It wouldn't need to be anywhere near as robust as ERWin, just a simple tool that can reverse engineer a database's from information_schema, and then present the table/column entity model and relationships.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • richardmgreen1

    SSCrazy Eights

    Points: 9949

    Sue_H wrote:

    Eric M Russell wrote:

    It is frustrating that the diagramming tool built into SSMS hasn't evolved in any significant way since 2005. One thing you can do is print to diagrams to PDF documents or use the Copy Diagram to Clipboard option to paste diagram image into something like MS Word. Also, maybe restore a copy of the database and then granting analysts db_owner access there.    

      They figured out how to address it - database diagrams no longer available starting with 18.0 preview RC1. Maybe that will change but check the deprecated features list: https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-2017 Sue  

    @sue - I couldn't see anything to say what they've replaced it with (if anything).  Any ideas?

     

    Eric M Russell wrote:

    I'm surprised that Microsoft hasn't invested in the development or acquisition of a good basic ERD tool over the years. It wouldn't need to be anywhere near as robust as ERWin, just a simple tool that can reverse engineer a database's from information_schema, and then present the table/column entity model and relationships.

    @eric - That would be nice (even if it was very basic) but it looks like it's not going to happen.

  • Sue_H

    SSC Guru

    Points: 90750

    I don't think they are replacing it with anything. Since they removed the database modeling in Visio 2013, the diagramming in SSMS was all that was left in MS products. A couple of years ago, MS created some add-in for database modeling in Visio Pro 365 but everything I've read said it doesn't work. And it has never been updated since it's release. So it doesn't look like it is anything they are too interested in developing. I really wish there was a basic type of tool like Eric describes.

    Sue

  • richardmgreen1

    SSCrazy Eights

    Points: 9949

    Thanks Sue.  Looks like we're back to pen and paper (of some description) then.

    We've found database diagrams really useful, especially when you've got complex databases, just to see how all the tables hook together.

  • Sue_H

    SSC Guru

    Points: 90750

    I've been playing around with some free alternatives, not interesting in any of the online only type of tools. Apex has a free data modeling tool you may want to check out:

    https://www.apexsql.com/sql-tools-model.aspx

    Sue

  • richardmgreen1

    SSCrazy Eights

    Points: 9949

    Thanks Sue - I'll have a look.

  • Eric M Russell

    SSC Guru

    Points: 125100

    Sue_H wrote:

    I've been playing around with some free alternatives, not interesting in any of the online only type of tools. Apex has a free data modeling tool you may want to check out: https://www.apexsql.com/sql-tools-model.aspx Sue

    Wow, I installed this, created a model by reverse engineering from an existing database, and it actually appears to be a worthwhile tool!

    I like that once the model is created, there is no longer a functional dependency on the source database, it stores the model separate from the database, unlike the SSMS diagramming tool that would write back to the source database every time you add a new table or relationship to the model.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Jeff Moden

    SSC Guru

    Points: 997123

    Eric M Russell wrote:

    Sue_H wrote:

    I've been playing around with some free alternatives, not interesting in any of the online only type of tools. Apex has a free data modeling tool you may want to check out: https://www.apexsql.com/sql-tools-model.aspx Sue

    Wow, I installed this, created a model by reverse engineering from an existing database, and it actually appears to be a worthwhile tool! I like that once the model is created, there is no longer a functional dependency on the source database, it stores the model separate from the database, unlike the SSMS diagramming tool that would write back to the source database every time you add a new table or relationship to the model.

    I'm sure it's possible but thought I'd ask before I cough up my personal information to get it... I hate Black backgrounds.  Is the color scheme configurable on the APEX product?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 997123

    Ok... cancel my last.  I went to the link posted and they didn't ask for a whole lot (just Name and email address) and so I took a shot at it.  First of all, the "Light" and "Blue" display options DO have a nice White background.  I did try the "Dark" option.  If they had used Black lines on the White background, things would have been great for me but the Blue lines that they use and the font colors are just a little pale on the White background and so, for this particular product, I have to admit the "Dark" background is easier to read.

     

    I also have to say I can't believe they offer this product for free.  I've only been playing with it against the Adventure works database for about an hour but I'm impressed with what it does and really like, for example, that it identifies AKs as well as making a line difference between "Identifying" and "Non-Identifying" relationships although I'd have preferred it if they had simply used different colored solid lines instead of dashed lines for "Non-Identifying" relationships.  Still, for a free product that does so much, I'm not going to complain.

     

    So far, I like the "Tree" layout the best but they have 5 others depending on what you're used to and they have 4 different "Edge routing" selections (I like "Polyline" the best, so far).  And it's super easy and nearly instantaneous to switch between "Tree" and "Hierarchical" to study relationships.  Like I said, I can't believe it's free.  And, I have it working on old Windows 7 and it very nicely reversed engineered Adventureworks 2008.

     

    I really liked the simplicity of "SQL Diagrams" but I have to admit that ApexSQL Model is simple as simple to use and has a whole lot more power without the danger of accidentally changing the underlying database for "What If" diagrams.

     

    The one thing that I am going to miss (and the product doesn't seem to have a way to do it) is to be able to start a new diagram with just one table and then tell it to add tables to the diagram than have relationships with that one table, like SQL Diagrams did.  Gonna miss that a whole lot.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • pietlinden

    SSC Guru

    Points: 62894

    I had a Visio license for 2000 version (it as a long time ago!) and it could reverse engineer databases. That would work, but it would be really nice to have a built-in tool to generate a map of the database. Just makes inherited databases so much easier to understand.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply