I use Mixed mode Authentication and I want my database users (in the Public role membership) to have the ability to JUST execute procedures that encrypt/decrypt data (using Certificate and Symmteric keys).
I have already given them Grant Execute on the procedures. What other Grants should I apply?
Thanx!
The specific permissions required are:
GRANT CONTROL ON CERTIFICATE :: [cert] TO [user] GRANT VIEW DEFINITION ON SYMMETRIC KEY :: [key] TO [user] However, rather than grant permissions directly on the Certificate and Key, I like to have two stored procedures - one for encryption and one for decryption. We generally have different users that perform the encryption and decryption. Generally, the application is encrypting records as they are inserted into the database. Then it's a different user that needs to decrypt them for reporting purposes. Both procedures are created WITH EXECUTE AS OWNER and the users are granted EXECUTE on the procedures.
This way no users have direct permissions on the Certificate and Key and no user can both encrypt and decrypt.
