I created one database called encrypt_test1, in that I created one table called Customer_data.
I encrypted single column, called "AccountNumber" in table called Customer_data.
Now I want to restrict other users to run the following command.
These other users are my team mates, and they have same access as me on "sqlserver/sqlserver2008r2" server.
right now they can run the following command, and can see encrypted data into decrypted form.
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
-- Now list the original ID, the encrypted ID
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
-- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
i want to restrict them , so that they can not run the above select query and can not see the decrypted data/