Mike Good (4/13/2009)
Marios, I like the way you think. This is going to take a little while to digest.One question I have right away, though: was this the answer to the problem of preventing DBAs from decrypting the data? You didn't come right out and say this. And I have no experience with digiatally signing code, so not sure what a DBA could or could not do with this.
Oh no, DBAs still have (for better or worse, and I am a DBA btw :-)) free reign over the encryption process. This scheme is more intended to block unauthorized access to the encryption objects (and data) by any non-sysadmin users, except through a sanctioned application.
As mentioned, in our organization we have a way of preventing users from issuing ad-hoc queries that mimick code calls from an application. For example, queries like "EXEC sprocName" cannot be issued by a non-sysadmin user from Management Studio, even if that same user has been granted EXEC permissions on that same sproc. That, along with code signing and granting the code-signing user SOLE access on the encryption objects, ensures that a non-sysadmin application user cannot issue T-SQL statements to open a symmetric key and encrypt/decrypt data ad hoc from Management Studio. I think that's a significant security improvement.
But I can't take credit for all this. Other links, including your article, helped me shape this scheme into how it stands at the moment.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]