We have an offshore support team coming online to support our servers as DBAs for 24x7 support. However, there are several instances that have a limitation due to contracts that limit offshore access to our data.

What security needs to be added to give them effective control over the servers as DBAs, but limits them to not being able to read/write data?

The instances are in mixed mode, but it would most likely be applied through a AD users group. SQL Server 2016 and Windows Server 2016.


It is not possible. Everything includes granting access, which means they can change whatever access you give them.

There are some encryption methods that can be used so DBA's can have full access but not have readable access to the data. I believe Transparent Data Encryption (TDE) is one option, but I am not an expert on it.

Make a list of "everything" you want the DBA's to do, then look at the rights required to do that. IMHO, a DBA who does not have sysadmin, is not a DBA.

  • Le's assume they do not have security rights. If that is the case, then is it possible. – bwilliamson Sep 6 '19 at 18:08
  • What would a DBA without security-rights do? – James Jenkins Sep 6 '19 at 18:09
  • 2
    @bwilliamson do you want them to fix anything if it is broken, or just record it and tell you in the morning? If you don't want them to fix it get some monitoring tools. – James Jenkins Sep 6 '19 at 18:23
  • 1
    @JamesJenkins We are using Redgate for our monitoring, but ideally they would have the ability to resolve issues that may arise overnight when the onshore DBAs are sleeping. Rerunning jobs, check and resolve blocking, expand data files and other things that do not require reviewing data. – bwilliamson Sep 6 '19 at 18:32
  • 1
    @bwilliamson I am DBA for hundreds of databases, that only honor keeps me from looking at the data of. I don't need to see it, so I don't look. If you can not trust someone to respect your data, you probably should not be trusting them to manage your database servers. – James Jenkins Sep 6 '19 at 19:57

What security needs to be added to give them effective control over the servers as DBAs

Seems the login required to be part of sysadmin as some of essential system stored procedures and most of DBCC commands are only accessible by sysadmin role member, and it's impossible to block the access for a login that is part of sysadmin role. You would may think of CONTROL SERVER and some additional Grant/Deny on top of it without having login added into sysadmin fixed role. This is what can happen in that case

I believe, it's good idea to implement alerts (that can notify via e-mail) and audits on certain events rather than looking to block an Ultimate User at Database server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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