September 21, 2010 at 1:48 pm
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
September 22, 2010 at 6:52 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply