Thanks Sean for taking time to help me out. Now I have two good solutions that I can work with.

This one ran much much faster... I need to testing and some addition...

I used lastnames and firstnames I got from census site so I expect the names to be different...

Lowell I tried the one suggested by you. It seem to take a long time for around 50K records. It ran for 15 minutes and I killed it. Do you...

Sean do I need to update using join using id? Also I found names list from census site... probably I can use that..

thanks Lowell.

yes correct randomize the firstnames and lastnames... thanks a lot.

bumping it up.. sorry

Ghanta thanks for the response.... yeah those users cannot see the decrypted data in the back-end; however, data is being accessed from front-end and I cannot restrict those users from...

I created another use and gave execute permission on the SPs that retrieves data. I gave permission to proc that opens the Symmetric keys. I deny view on Symmetric Keys...

I was able to grant user permission to select, update, insert with encryption/decryption to user by giving just VIEW permission on the symmetric key and not granting Control on Certificate....

Seems like I have to Grant Control on CERTIFICATE to user to give the user the ability to encrypt and decrypt.

If I just grant View Definition on the Symmetric...

Ghanta (11/29/2011)

---

Only grant access to the symetric keys to those users/roles that absolutely need to decrypt any column.

Grant execute only on the stored procedures that are required to retrieve...

Thanks a lot Sturner.

In that case a user who has EXECUTE permission to Stored Procedure but do not have permission in the Symmetric Keys and Certificates will be able to execute the Stored...

Thanks a lot Sturner.

Yes and I think you missed onething I should grant symmetric keys to user/role that will do insert on that table as well rite...