2

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.

Schemas 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?

ComfortablyNumb
  • 1,448
  • 10
  • 37
  • 64
  • 1
    Is your application connection string using the same user login as the login when you connect natively via SSMS? – Chris Marisic Jul 07 '11 at 13:54
  • good point. I'm using Windows authentication. – ComfortablyNumb Jul 07 '11 at 16:00
  • With your statement here, most likely the error message you are getting is exactly accurate. The application user does not have access to the symmetric key 'myKey'. Hopefully one of the MSSQS guys here will be able to point you in the right direction on the sql commands to add those permissions. – Chris Marisic Jul 07 '11 at 16:03
  • just tried it using the same user login and it's failed with the same error message. So it looks like I've screwed up my permission in the database. – ComfortablyNumb Jul 07 '11 at 16:05
  • What I really don't get is I can add a Public or Guest permission to the SP in question but not the user login - just wont stay in the permissions list. – ComfortablyNumb Jul 07 '11 at 16:10
  • I feel for you, I find the permissions model in Sql server to be the most unwieldy system ever created by Microsoft. You might want to try posting on the social msdn forums for sql server for help too. – Chris Marisic Jul 07 '11 at 17:09

1 Answers1

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

MartinC
  • 447
  • 3
  • 8