Column-Level Permissions

While you're encrypting data, you may find a table like your credit card data where users should be able to see all columns except the credit card number. If this is the case, you have the option to protect data at the column level. To deny rights to an individual column, you can use the DENY SELECT statement. If you take the earlier example and deny the LowPrivUser rights to the CardNumber column in the dbo.CustomerCreditCards table, it would look like the following code:

 DENY SELECT (CardNumber) on dbo.CustomerCreditCards to LowPrivUser 

If the LowPrivUser attempted to pull the CustomerID column out of the table, he would experience no problem. The minute he tried a larger query, as shown below, he would experience a permission problem:

 EXECUTE AS USER = 'LowPrivUser' SELECT CustomerId,        dbo. CardNumber     FROM dbo.CustomerCreditCards REVERT 

This would return the following error. Any SELECT * statement would also fail with the same error.

 Msg 230, Level 14, State 1, Line 2 SELECT permission denied on column 'CardNumber' of object 'CustomerCreditCards', database 'EncryptionExample', schema 'dbo'.