Encryption Role

• Gill Rowley

  SSC Eights!

  Points: 965

  More actions

  September 21, 2010 at 1:48 pm

  #238575

  We're creating a Java web project with SQL Server 2005 as the database. We've created different logins/users (logins are used in more than one database) based on what the task is, whether it's retrieving data or writing data. We've limited the permissions the two users have to db_datareader and db_datawriter. I've also created a role call db_executor and granted EXECUTE rights to it, and assigned it to the two users so they can call stored procedures from the Java code.

  At times we need to read or write encrypted information. It seems I can't do that unless I make the user a database owner. Is there something out there similar to:

  CREATE ROLE db_encrypt

  GRANT ENCRYPTION TO db_encrypt

  I know this doesn't work, but I'm hoping there's something that does this. I really don't want to make these two users database owners.

  Thanks.

  -Gill

• Gill Rowley

  SSC Eights!

  Points: 965

  More actions

  September 22, 2010 at 6:52 am

  #1225528

  Figured it out. It has to be done at the cert and key level.

  CREATE ROLE db_encryption

  GRANT CONTROL ON CERTIFICATE::<certName> TO db_encryption

  GRANT CONTROL ON SYMMETRIC KEY::<keyName> TO db_encryption

  Ther are other permissions other than CONTROL, but for now this works for me.