Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
Briliant! Thanx! –  PanosPlat Mar 18 '13 at 6:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.