使用加密的 SQL Server 2008 存储过程的权限问题
我已经安排了一个具有三个模式的数据库。在“数据库用户”下，我已将三个模式勾选为我正在连接的用户的“拥有模式”（当时认为这是一个好主意）。
但是，这似乎不允许我将有问题的数据库用户添加到存储过程的权限中，这些存储过程也在相同的三个模式（不是 dbo）下。我选择 db 用户，选择执行，然后单击确定。回去发现权限没了。
我很确定我已经搞砸了数据库，因为数据库用户（常规）下的每个勾号都是灰色的。
在开发 ASP.Net 网站时，我没有遇到任何权限问题，直到我尝试通过客户将数据保存到 INSERT 存储过程中，该存储过程还对客户的密码进行加密。当调用 SP 时，出现以下错误：
找不到对称密钥“myKey”， 因为它不存在或者你存在 没有权限。
我可以在SSMS中完美运行SP，并且密码加密得很好。
我在哪里缺少提供权限？灰色的模式是问题所在吗？或者这是 ASP.Net c# 问题？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您将需要使用 EXECUTE AS 来更改有权打开加密密钥的 db 用户的权限。
如果您在使用 EXECUTE AS USER='ASPUser' 后尝试执行 SP，您应该会遇到相同的问题。
这是一个视频链接，其中包含有关安全 EXECUTE AS 和 SQL 注入
和 EXECUTE AS 过程的 MSDN 条目 和 以用户身份执行
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