SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Feivel
Feivel
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6103 Visits: 2010
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)

Group: General Forum Members
Points: 884916 Visits: 47960
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)

Group: Administrators
Points: 611967 Visits: 21189
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Feivel
Feivel
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6103 Visits: 2010
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
Jeff Moden
SSC Guru
SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)

Group: General Forum Members
Points: 884916 Visits: 47960
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. Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)

Group: Administrators
Points: 611967 Visits: 21189
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214726 Visits: 18591
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
Perry Whittle
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214726 Visits: 18591
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
Jeff Moden
SSC Guru
SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)

Group: General Forum Members
Points: 884916 Visits: 47960
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)

Group: Administrators
Points: 611967 Visits: 21189
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search