Managing SYSADMIN access to data

Question

Managing SYSADMIN access to data

garth.thomas

Valued Member

Points: 62
More actions
January 29, 2010 at 2:36 am

#215135

Hi,
We have an issue where we want to protect sensitive data from our DBA's who by the nature of there work require Sysadmin access to the MS 2005/2008 db's and are therefore part of the sysadmins group. We have a few columns in tables in particular that we want to prevent access to so that these are not viewable to even the sysadmins. We have tried to Deny select rights to the columns but the sysadmin rights seem to overrule the deny.
Does anyone know if it is possible to do this or a work around to achieve this?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

sql-638562 Valued Member Points: 57 More actions · Answer 1

Hi Garth,

SysAdmin provides full access to the entire system. Have you considered using Column data encryption through certificates?

This method scrambles the data within and be unreadable even to a SysAdmin user. However, this is a fair amount of develpoment required to implement.

garth.thomas Valued Member Points: 62 More actions · Answer 2

Thanks - we are working towards encrypting the sensitive data but requires applicaiton changes as well so going to take us a while to get there. In the mean time we are trying to figure out an interim solution.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Nothing can be denied to a sysadmin.

You need encryption here, but not using SQL Server's certificate-based encryption. The sysadmins will have full rights to the certificates and hence will be able to decrypt any data that's encrypted with a key stored in the database.

You need to encrypt in the application, so that the data that comes to SQL is already encrypted. The keys must be stored somewhere other than the SQL Server database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass