How would you lock down a SQL DBA to have local admin on the SQL box

Question

How would you lock down a SQL DBA to have local admin on the SQL box

Feivel

SSCertifiable

Points: 7524
More actions
February 20, 2018 at 1:53 pm

#336195

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 1

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.

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

For Perf counters, there are ways to let non-admins get them: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc771692(v=ws.10)
For attach/detach, just SQL permissions are needed, though the admin might need access to the folders to move files in/out.

For install, I think you'd need admin permissions. At least temporarily. You have to set permissions and create service accounts. This won't be something you can easily do by proxy. Of course, you could move to Chef/Puppet/DevOps style installs, which might alleviate some issues.

Stop/start services, you can use sc (https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc742037(v=ws.10)), and assign some rights, but this wouldn't be as easy as using SSMS.

Feivel SSCertifiable Points: 7524 More actions · Answer 3

Steve Jones - SSC Editor - Thursday, February 22, 2018 9:51 AM
For Perf counters, there are ways to let non-admins get them: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc771692(v=ws.10)
For attach/detach, just SQL permissions are needed, though the admin might need access to the folders to move files in/out.
For install, I think you'd need admin permissions. At least temporarily. You have to set permissions and create service accounts. This won't be something you can easily do by proxy. Of course, you could move to Chef/Puppet/DevOps style installs, which might alleviate some issues.
Stop/start services, you can use sc (https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc742037(v=ws.10)), and assign some rights, but this wouldn't be as easy as using SSMS.

Thanks! Steve. In short we do need admin privileges for some task if not all the task...right?

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 4

ffarouqi - Thursday, February 22, 2018 10:59 AM
Steve Jones - SSC Editor - Thursday, February 22, 2018 9:51 AM
For Perf counters, there are ways to let non-admins get them: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc771692(v=ws.10)
For attach/detach, just SQL permissions are needed, though the admin might need access to the folders to move files in/out.
For install, I think you'd need admin permissions. At least temporarily. You have to set permissions and create service accounts. This won't be something you can easily do by proxy. Of course, you could move to Chef/Puppet/DevOps style installs, which might alleviate some issues.
Stop/start services, you can use sc (https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc742037(v=ws.10)), and assign some rights, but this wouldn't be as easy as using SSMS.
Thanks! Steve. In short we do need admin privileges for some task if not all the task...right?

No... only if you want the DBA to be able to do their jobs. 😉

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 5

Yes, despite Jeff's joke. For some things you do need this if the DBA directly does work. If you automate some things, perhaps you can reduce the number dramatically, or eliminate it, but it's work to do so.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

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

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

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

some of these have elevated permissions, no way round that.
are you really unable to trust the admins that much?
Which users are to remain in control of the instance in the event you lock down?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 8

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)

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 9

Hmmm, OK. I tried this:

CREATE LOGIN JoeDBA with password = 'xxx' GO GRANT ALTER ANY DATABASE TO JoeDBA
I then opened a new window, logging in as JoeDBA and ran:
EXEC sys.sp_detach_db @dbname = 'Bob'

Got this:
Msg 916, Level 14, State 1, Line 1
The server principal "JoeDBA" is not able to access the database "Bob" under the current security context.

Then tried this in my sysadmin connection:
USE bob GO CREATE USER JoeDBA FOR LOGIN JoeDBA GO ALTER ROLE db_owner ADD MEMBER JoeDBA

Returned to my JoeDBA connection and was able to detach the database.

ALTER ANY DATABASE is not sufficient, but db_owner for a db is. This means you'd need to ensure each login had that permission in each database to detach.

I also tried dbcreator and serveradmin (separately) for the login. neither allows me to detach a database. I suspect sysadmin OR db_owner in a specific database allow detaching. Of course, if you add the user to model and include db_owner, that would solve that permission set.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 10

Steve Jones - SSC Editor - Thursday, March 1, 2018 2:43 AM
Hmmm, OK. I tried this:
CREATE LOGIN JoeDBA with password = 'xxx' GO GRANT ALTER ANY DATABASE TO JoeDBA
I then opened a new window, logging in as JoeDBA and ran:
EXEC sys.sp_detach_db @dbname = 'Bob'
Got this:
Msg 916, Level 14, State 1, Line 1
The server principal "JoeDBA" is not able to access the database "Bob" under the current security context.
Then tried this in my sysadmin connection:
USE bob GO CREATE USER JoeDBA FOR LOGIN JoeDBA GO ALTER ROLE db_owner ADD MEMBER JoeDBA
Returned to my JoeDBA connection and was able to detach the database.
ALTER ANY DATABASE is not sufficient, but db_owner for a db is. This means you'd need to ensure each login had that permission in each database to detach.
I also tried dbcreator and serveradmin (separately) for the login. neither allows me to detach a database. I suspect sysadmin OR db_owner in a specific database allow detaching. Of course, if you add the user to model and include db_owner, that would solve that permission set.

Those were my experiences, as well. I couldn't find anything in BOL either locally or online that said that ALTER ANY DATABASE would allow someone to attach or detach databases and the documentation does seem to clearly state that either sysadmin or DBO privs are necessary to do attach or detach. That, not withstanding, Perry is a pretty smart cookie on this type of stuff and wondered what trick he might have up his sleeve for ALTER ANY 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)