2

i have made columns in some of the tables encrypted in sql server 2008. Now as i am a db owner i have the access to encode and decode the data using the symmetric key and certificate. But some other users have only currently datareader and datawriter rights ,and when they execute any SP referring the logic which uses the key and certificate "User does has not right on the certificate to execute". What rights / exact permission should i grant them just to solve this problem

paranjai
  • 167
  • 6

1 Answers1

3

The following command is used to grant users rights to a certificate:

GRANT CONTROL
ON CERTIFICATE :: certificate_name
TO username
Shane
  • 1,869
  • 4
  • 20
  • 34
  • it doesn't works , sql server raises an error Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'to'. – paranjai Mar 25 '10 at 11:40
  • Sorry I forgot to include the :: before certificate_name. It is actually required so it may be causing your issue. – Shane Mar 25 '10 at 13:48
  • I think I mucked something else up. Execute is not available on certificates. I believe you need to use CONTROL for your users to be able to access the cert. Sorry for the mistakes but it's been a while since I've used this and I was just going off the top of my head. – Shane Mar 25 '10 at 14:02