I Cannot 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 am asymmetric key for encryption by password. I am not using a master key because I want to keep the password on seperately on the web server so a hacker cannot get 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?