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