This piece was originally published on Jul 8, 2009. It is being re-run as Steve is away on sabbatical.
The other day I noticed a post where someone was asking a question that I’ve seen asked often. I still haven’t seen a great solution, and I expect this question will continue to be asked for some time to come. The poster asked how to securely set up a database to prevent administrators from accessing the data.
Prior to SQL Server 2005 this was pretty much impossible. The sysadmin group was considered to be like a god inside the SQL Server and could access most anything. With 2005 there are more restrictions you can place on the administrator, and with the addition of encryption capabilities, you can prevent casual access to certain data.
However it’s not easy, and most of the time the system administrator still has access to data for tuning, troubleshooting, disaster recovery, etc. That makes sense sometimes, and I feel that you really have to trust your administrator with a lot of responsibility and discretion. Sometimes, however, it’s just not appropriate for the person that runs the server to see other data. Salary information, among other data, sometimes just isn’t the business of the administrator.
I don’t know how you handle this. Does the administrator just get access to the database as a container, able to attach it, back it up, restore it, and perform basic functions? Are they limited to setting security for users, but unable to access the objects themselves? I’m not even sure that’s a valid way to handle things since the administrator can always set up a dummy account for themselves, or change someone’s password and access data.
To me the best solution for secure administrator access is to have a second person audit all actions performed by a sysadmin. Kind of a default trace for sysadmins that is always running, and is not accessible to the administrator. Even then, it’s probably only something that works in larger companies where you have enough people to dedicate to the task of reviewing things. Maybe policy based management (PBM) will help here at some point, limiting the access to data by administrators in an easy to understand manner that a manager of some sort can understand and audit.
There’s no good solution now other than to trust your administrators to responsibly manage data, and that means hiring responsible people for the job.
Steve Jones
