2

Our organisation employs a third party company to do maintenance on our database. To be honest, currently they act as our DBA's since we do not have one.

In order to do the maintenance work, they are using the SA user.

Is there a way to make them a new user they can use, to do all of the administrative operations without being to actually see the data. So, they can see all the tables and columns, but they will be unable to see any row.

If I'm missing something, please feel free to enlighten me since my DBA knowledge is fairly poor.

Improve this question
3
  • If you want them to do "Admin Task", they would require admin privileges. And if they have admin privileges they can see, query, change, update etc anything in your database. Depending on the tasks they need to perform you can give create new account with required privileges.
    – Shanky
    Commented Jun 17, 2016 at 12:22
  • My question is can they create/drop tables, columns, views, triggers, basically do everything without being able to see the data.
    – John
    Commented Jun 17, 2016 at 12:27
  • Yes they can do "any" thing if they are administrators in SQL Server. Yes you can enable Auditing feature to catch some of the activities
    – Shanky
    Commented Jun 17, 2016 at 12:32

1 Answer 1

Reset to default
5

Sysadmins can see all the data in the instance and there is no way to restrict the permissions of members of the sysadmin server role.

The only possible way to hide data from sysadmins is by encrypting it, using a key that is not available on the server. This is exactly what the Always Encrypted feature in SQL Server 2016 does.

In SQL Server 2008 your options are very limited: you can use a custom encryption layer, but you would have to code it yourself. Also, unlike what happens in Always Encrypted, there would be nothing to enforce that the columns you want to encrypt actually contain only encrypted data (the application has to take care of that).

That said, this is not only a tech issue: the company that does maintenance for you is a trusted partner, so you can talk to them straight and ask not to look at the data. You can also set up auditing to catch unauthorized reads/writes. However, it's a grey area: if they're troubleshooting a performance issue, for instance, they will probably run some queries and see some data.

Bottom line is: if you don't trust a third party, don't let them manage your instances.

Improve this answer
4
  • Thanks for your answer. Unfortunately,the situation is a little more complicated, that is why we require such a solution. Could you point me in the right direction where I can look for setting up such auditing?
    – John
    Commented Jun 17, 2016 at 13:05
  • First of all, auditing requires Enterprise Edition. Are you on Enterprise?
    – spaghettidba
    Commented Jun 17, 2016 at 13:07
  • Yes we are on Enterprise.
    – John
    Commented Jun 17, 2016 at 13:11
  • This whitepaper will put you in the right direction: technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
    – spaghettidba
    Commented Jun 17, 2016 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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