Managing Access Rights to an encrypted column

Question

I've been searching for a way to encrypt a column in SQL Server and manage the access rights to the encrypted column.

There are many articles about the former (e.g. here), but I can't find anything that explains the latter.

I've been experimenting with a local instance of SQL Server and using SSMS to connect to the DB.

Using a user associated with my Windows Account (Windows Authentication) I created a simple database with one table, then encrypted one of the columns using "Always Encrypted", by selecting "Encrypt Columns" from the SSMS context menu. The encrypted column doesn't contain any keys/indexes.

What I intend to achieve is to create another user and grant it with rights to read and write data to the table with the encrypted column, but NOT being able to decrypt or query the encrypted column.

Then I created a local SQL Server account and grated it with read and write access to the db. However, the local user can still access the data from the encrypted column (I use the "Enable Always Encrypted" connection parameter).

Question

How can I tweak the access rights for the local user, so that it has read/write access to the table, but can't get the decrypted content of the encrypted column?

Answer 1

If the user doesn't has access to the encryption key, they will never see the data in plaintext. The database role db_datareader and db_datawriter will not help in this case. This will allow to user to read encrypted data.

See also Overview of key management for Always Encrypted