Permission Issue for SQL Server 2008 Stored Procedure Using Encryption

Question

I have arranged a database with three schemas. Under Database User, I have ticked the three schemas as Owned Schemas of the user I'm connecting via (thought it was a good idea at the time).

However, this seems to be disallowing me to add the db user in question to permissions for stored procedures which are also under the same three schemas (not dbo). I select the db user, select execute and then click OK. Go back and the permission has gone.

I'm pretty sure I've screwed the database up as each tick under Database User (General) is greyed out.

I've not had any problems with permissions whilst developing my ASP.Net site until I was trying to pass customer data to an INSERT stored procedure that also encrypts the customer's password. When the SP is called I get the following error:

Cannot find the symmetric key 'myKey', because it does not exist or you do not have permission.

I can run the SP perfectly in SSMS and the password is encrypted fine.

Where am I missing providing permissions? Are the greyed out schemas the problem? Or is this an ASP.Net c# issue?

Answer 1

You will need to use EXECUTE AS to change the permission to a db user with rights to open the encryption key.

If you try executing the SP after using EXECUTE AS USER='ASPUser' you should get the same issue.

Here is a link a video with the details on Security EXECUTE AS and SQL Injection and MSDN Entry on EXECUTE AS for procedures and EXECUTE AS USER