Home Forums SQL Server 2017 SQL Server 2017 - Administration How would you lock down a SQL DBA to have local admin on the SQL box RE: How would you lock down a SQL DBA to have local admin on the SQL box

  • February 28, 2018 at 2:36 pm

    #1981557

    Perry Whittle - Wednesday, February 28, 2018 7:58 AM

    Jeff Moden - Wednesday, February 21, 2018 6:55 AM

    ffarouqi - Tuesday, February 20, 2018 1:53 PM

    I would like to know if we can create a separate group or what permissions on the server should be granted for the DBA to make sure he/she can perform the duties such as the ones listed below in case if we lock down local admin rights on the box due to management and security reasons.

    - SQL database attach/detach
    - SQL installation, service pack updates etc.
    - Monitoring performance metrics
    - restarting and stopping sql server

    Just attaching/detaching a database requires either sysadmin privs or DBO privs, which I'm thinking is beyond the level of privs that you want such folks to have because it will allow them do either do anything to the server or anything to the database they're assigned.

    Neither require sysadmin, create database permission required at server level to create and attach a db
    Alter any database at server level required to detach a database, members of dbo database role can also detach the db

    Thanks, Perry.  I based my comment on needing either sysadmin or dbo privs based on the "Permissions" or "Security" sections of the following. 
    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-detach-db-transact-sql
    https://docs.microsoft.com/en-us/sql/relational-databases/databases/detach-a-database

    With the idea of "teach a man to fish" and that my Google-fu apparently is lacking for this particular search, how do you find out where someone can alter a database to detach it without having sysadmin or dbo privs on the given database?  For example, how do you find where it is documented that ALTER ANY DATABASE will allow you to detach a database?

    --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.

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