Post reply

Cannot decrypt or encrypt without making user a db_owner

  • February 4, 2006 at 4:51 pm

    #189075

    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?

  • February 7, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • February 9, 2006 at 1:02 pm

    #619906

    I got it to work.

    I put it in a stored procedure, but the key was to grant CREATE ASYMMETRIC Key rights to the user.

    Under securables, select DATABASES as the type of object.  Browsw and select the specific database you are using.  The permission should appear in the list.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply