How to make encrypted data accessible to one specific user?

• John Hård

  Valued Member

  Points: 53

  More actions

  August 31, 2006 at 6:24 am

  #92375

  Hello people.

  I am encrypting a few columns in a table using a certificate. The statements I used are these:

  CREATE

  MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxx';

  CREATE

  CERTIFICATE TestCert AUTHORIZATION MyUser

  WITH

  SUBJECT = 'TestCertName',

  START_DATE

  = '2006-08-28';

  UPDATE TestTable

  SET

   EncryptedData = EncryptByCert(Cert_ID('TestCert), 'Text to encrypt');

  Now, I thought that AUTHORIZATION MyUser made sure that only MyUser have access to the certificate, but when I fetch the data I can use any Login and decrypt the data. Like this:

  SELECT CONVERT(varchar, DecryptByCert(Cert_ID('TestCert'), EncryptedData))

  FROM TestTable

   

  My knowledge about certificates is (as you can see) not very good. Can anyone tell me how to encrypt data and make sure that only one user can decrypt this data?

  Thank you very much.

• Johan Bijnens

  SSC Guru

  Points: 135551

  More actions

  August 31, 2006 at 6:48 am

  #657780

  http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx

  explains a bunch of it !!

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• John Hård

  Valued Member

  Points: 53

  More actions

  September 6, 2006 at 11:38 pm

  #658968

  Thank you very much for the fast reply! I'll give it a try.