Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 6 down vote accepted

From the documentation:

  • Although any user with access to a database can create a diagram, once the diagram has been created, the only users who can see it are the diagram's creator and any member of the db_owner role.
  • Ownership of diagrams can only be transferred to members of the db_owner role. This is only possible if the previous owner of the diagram has been removed from the database.
  • If the owner of a diagram has been removed from the database, the diagram will remain in the database until a member of the db_owner role attempts to open it. At that point the db_owner member can choose to take over ownership of the diagram.

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:

CREATE PROCEDURE dbo.sp_helpdiagrams
(
    @diagramname sysname = NULL,
    @owner_id int = NULL
)
WITH EXECUTE AS N'dbo'
AS
BEGIN
    DECLARE @user sysname
    DECLARE @dboLogin bit
    EXECUTE AS CALLER;
        SET @user = USER_NAME();
        SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
    REVERT;
    SELECT
        [Database] = DB_NAME(),
        [Name] = name,
        [ID] = diagram_id,
        [Owner] = USER_NAME(principal_id),
        [OwnerID] = principal_id
    FROM
        sysdiagrams
    WHERE
        (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
        (@diagramname IS NULL OR name = @diagramname) AND
        (@owner_id IS NULL OR principal_id = @owner_id)
    ORDER BY
        4, 5, 1
END

So you can see this matches the documentation.

Now a couple of workaround ideas:

  • In a Logon trigger, update the 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.
  • Use a trigger on the 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):

CREATE TRIGGER dbo.sysdiagrams_distribute
ON dbo.sysdiagrams
WITH EXECUTE AS N'dbo'
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @p TABLE(principal_id INT, name SYSNAME);

  INSERT @p SELECT principal_id, name
    FROM sys.database_principals
    -- change this list:
    WHERE name IN (N'test_blat_user', N'test_blat_user2', N'dbo');

  UPDATE d 
    SET [version] = i.version, definition = i.definition
  FROM inserted AS i
  CROSS JOIN @p AS p
  INNER JOIN dbo.sysdiagrams AS d
  ON d.name = i.name
  AND d.principal_id = p.principal_id;

  INSERT dbo.sysdiagrams(name, principal_id, version, definition)
    SELECT i.name, p.principal_id, i.version, i.definition
    FROM inserted AS i
    CROSS JOIN @p AS p
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.sysdiagrams WHERE name = i.name
      AND principal_id = p.principal_id
    );
END
GO

After creating a couple of diagrams, here is what an abridged version of Object Explorer looked like for these users:

enter image description here

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.

share|improve this answer
    
Very thorough. think i will give your last suggestion a try. thanks a lot –  Steve Jun 7 '13 at 12:32
    
I have to say, I think it is crazy that only DBO users can see the diagrams. There are so many permissions in SQL Server, they really could not make some way to set permissions on diagrams? –  Vaccano Aug 16 '13 at 17:06

As per BOL, an account with database owner dbo privileges is required. More info here.

So, the user who created it, or a member of the db_owner role can open diagrams.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.