我可以在 SQL Server 2008 或 C# 中在运行时限制用户的权限吗？
我正在开发一个网站，该网站托管在一个共享托管网站上，该网站为我提供了 1 个数据库连接。
当然，该登录名具有读取和写入的完全访问权限（因为它是我用来构建表等的连接）。
但是，我希望允许用户能够编写临时 SQL 查询，但我想严格限制它们......作为示例，不能写入任何表。
所以我的问题是 - 有没有办法在运行时限制任何功能......也许作为连接字符串中传递的设置？
或者我可以用 C# 做些什么？ （例如：我可以启动事务并将其回滚）？
I have a website that I'm working on, and its hosted on a shared hosting site that gives me 1 database connection.
Of course, that login has full access to read and write (as it is the connection I use to build my tables, etc).
However, I want to allow users to be able to write ad-hoc SQL queries, but I want to limit them severely... as an example, not being able to write to any tables.
So my question is - is there ANY way to limit ANY functionality at all at runtime... perhaps as a setting passed in the connection string?
Or is there something I can do in C#? (example: I can start a transaction and roll it back)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您是否有理由无法为每个用户创建新的用户帐户？然后使用内置的服务器安全性来限制每个用户可以执行的操作。
例如，您可以为新的受限用户设置角色组，然后使用表安全性来限制访问。
Is there a reason you can't create new user accounts for each user? Then use the built in server security to limit what each user can do.
You could, for example, set up role groups for the new limited users, and then use table security to limit access.
您可以使用 EXECUTE AS t-sql 语句来切换执行上下文用于另一个用户的会话。您可以使用WITH COOKIE 锁定执行上下文，直到您通过REVERT 语句提供cookie 值。
You can use the EXECUTE AS t-sql statement to switch the execution context for the session to another user. You can use WITH COOKIE to lock the execution context down until you supply the cookie value with your REVERT statement.
因此，看来您无法限制对与您完全相同的用户的访问...所以我正在做的解决方法是我创建了另一个数据库（一个空白数据库），并且我将包装所有我将强制回滚的事务中的用户查询。
这是一个令人讨厌的解决方案，但正如 Factor Mystic 在他的评论中所说 - “获得更好的托管”可能是最好的解决方案（意思是 - 这里的问题在于托管公司）。
So it appears that you cannot limit access to the very same user that you are... so what I'm doing as a workaround is I've created another database (a blank one) and I'm going to wrap all of the user queries in a transaction that I will force to rollback.
It's a yucky solution, but as Factor Mystic said in his comment - "get better hosting" is probably the best solution (meaning - the problem here lies with the hosting company).