permissions for Dev DBAs


  • We have two main groups of DBAs: dev and ops.  In the past, the ops DBAs owned production SQL Servers, and the dev DBAs had sysadmin and local OS admin on the dev and test SQL Servers.  Now, the ops DBAs want to own ALL servers, only granting the dev DBAs db_owner at the database level.  I feel handcuffed.  I'm currently requesting ALTER TRACE so I can run traces and VIEW SERVER STATE, so I can utilize DMVs effectively.  Wondering what else I will need to do my job.

    My question is, "Is this normal or a best practice"?   Seems to me that a dev DBA should be able to fully admin a dev server.  I might be able to understand more restrictions in QA and higher environments (maybe even dev integration), but I feel the need for a SQL Server instance where developers and I can truly architect and develop SQL Server solutions.  I am a certified DBA, btw, and used to be able to admin ALL of the environments.

    Community thoughts on this?

    Selasa, 24 April 2018 17.56

Semua Balasan

  • I guess the way this ends up is that the devs find their own instance somewhere which they don't tell the ops people about, and where they can roam freely.

    I would indeed feel handicapped in an environment where I am not sysadmin. VIEW SERVER STATE is a bare minimum, and obviously traces can be important. I should add that, the advent of Query Store to some extent mitigates the situation, since here you can find query plans and performance statistics.

    Then again, there is also the situation where you develop something which needs server-level permission, for instance BULK INSERT.

    Selasa, 24 April 2018 21.54
  • While all those are valid points, ask yourself one thing for example:

    How WILL you know that something that you need to do needs more than "db_owner"?

    If the Ops Admin is responsible for the server, doesn't it make sense that he is the only one who can "mess" with it?

    And if you find out you need to be able to do tings at server level like BULK operations, then you need to develop a way that will work in production anyway. You can wait till the test on the Test/QA system fails, or you find out already when trying to implement.

    I know some devs will hate me for such an opinion, but I am simply trying to give some arguments for another angle. 

    And why not wait until you really hit another border and then request for a solution (permission or workaround)

    Maybe this article is of interest for you: Separation of Duties (SoD) and role-based security conception in SQL Server - although I have (deliberately) left out the example for Devs :-)

    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.

    Kamis, 03 Mei 2018 18.00