April 17, 2019 at 2:23 pm
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
April 17, 2019 at 5:20 pm
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
April 17, 2019 at 5:38 pm
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
April 17, 2019 at 8:15 pm
From here: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms186345(v=sql.110)
Need db_owner.
April 18, 2019 at 8:25 am
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.
April 18, 2019 at 1:11 pm
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
April 18, 2019 at 1:30 pm
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?
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.
April 18, 2019 at 2:03 pm
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
April 18, 2019 at 2:17 pm
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.
April 18, 2019 at 2:59 pm
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
April 18, 2019 at 3:24 pm
Thanks Sue - I'll have a look.
April 19, 2019 at 5:49 pm
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
April 21, 2019 at 7:16 pm
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
Change is inevitable... Change for the better is not.
April 21, 2019 at 8:55 pm
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
Change is inevitable... Change for the better is not.
April 21, 2019 at 10:34 pm
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 16 total)
You must be logged in to reply to this topic. Login to reply