how to encrypt and decypt as a IUSR with read and write only rights

How to decrypt or encrypt without making user a db_owner. It is for a web application and I do not want make the web user a db_owner. Is there a way to make this work without making the user a db_owner. Currently the user is a db_datareader and db_datawriter.

I created an asymmetric key for encryption by password. I am not using a master key because I want to keep the password seperately on the web server, so a hacker cannot get access to both if database gets hacked.

These are the steps I took when I logged in to SQL server management studio using windows authentication:

CREATE ASYMMETRIC KEY ccnumber WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'password';

INSERT INTO Payments (CreditCardNumber,enc_CreditCardNumber)
values( '458724124',
EncryptByAsymKey(AsymKey_ID('ccnumber'), '458724124') )

SELECT CONVERT(varchar(50), DecryptByAsymKey( AsymKey_Id('ccnumber'), enc_CreditCardNumber, N'password' ))
AS Creditcardnumber , Creditcardnumber
FROM payments where Creditcardnumber = '458724124'

When I use the above select statement it works if I make the user a db_owner but I get null if the user is just db_reader and db_writer.

Is there a way to do encryption without making the user a db_owner?

Yes, you can encrypt without being a db_owner. Note that you should not use asymmetric key encryption for encrypting data. You should use symmetric keys to encrypt data and asymmetric keys to protect other keys or for signing code.

To encrypt and decrypt with an asymmetric key, you need to grant CONTROL permission on the key (VIEW is sufficient for encryption, but for decryption you need CONTROL and knowledge of the password).

For encryption and decryption with a symmetric key, the user must be able to open the symmetric key (see permissions section of this BOL article: http://msdn2.microsoft.com/en-us/library/ms190499.aspx). You may also find the following blog post useful: http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.

Thanks
Laurentiu

sql