I have recently set up SSDT for our developers to use. We enforce changes to our dev databases are made through SSDT by limiting the permissions that each developers has when connected to the server (db_datareader, db_datawriter). Within SSDT we publish our changes to the database using a deployment script which connects using a log on with elevated permissions.
My question. Given that we have gone to this length to lock down the database (to stop schema drift); is there any way that the developers can view the diagrams on this database without having to have db_owner permission? I know that each developer can create and view his or her own diagrams but i want them to be able to view all of the diagrams, which have been created by many different developers.
I don't think this will help but we are running sql server 2012
Any help will be greatly received.
Best Answer
From the documentation:
So it seems that you won't be able to do it with lower roles like
db_datareader
.Behind the scenes, here is what Management Studio is calling to drive the list:
So you can see this matches the documentation.
Now a couple of workaround ideas:
principal_id
of all diagrams to be the current login. This means they will have access to all diagrams until the next person logs in. Not optimal.sysdiagrams
table itself (it's not really a system table), and whenever a diagram is created or updated, add / update a copy for each principal (with their user name appended). Not optimal either, and you could have people overwriting each other's diagrams all day long.Here is an idea of the second workaround - all you really have to maintain here is a list of the database principals that you want to be able to access the diagrams (you'll also want to have something to clean up diagrams that have been deleted, and also some periodic maintenance that deletes diagrams for principals that have been deleted):
After creating a couple of diagrams, here is what an abridged version of Object Explorer looked like for these users:
Now,
dbo
will collect a whole bunch of copies of diagrams, which maybe isn't necessary, but you probably want those to be the "master" in most circumstances.