如何允许SQL Server用户插入/更新/删除数据，但不改变模式？ [英] How to permit a SQL Server user to insert/update/delete data, but not alter schema?

问题描述

我的应用程序（C＃，ASP.Net）需要在DB中插入，更新和删除数据，并运行存储过程。我需要阻止它修改数据库模式 - 没有更改表，创建或删除，没有更改存储过程。

我需要向应用程式使用者授予哪些权限组合？只是'select'不工作，因为它需要在表中插入/更新/删除数据。

如何检查特定登录的权限和访问权限？
如何授予或拒绝登录的权限和访问权限？
我需要授予一个新用户（登录）的权限，以只访问一个数据库。

使用SQL Server 2008 R2和SSMS。

解决方案

如果你真的想在对象层控制这个，你可以这样做：

  GRANT SELECT，UPDATE，INSERT，DELETE on dbo.table TO user; 
  

在架构级别：

  GRANT SELECT，UPDATE，INSERT，DELETE ON SCHEMA :: dbo TO user;但是，理想情况下，不允许对表使用特定的DML，并且通过存储来控制所有的DML（$ d $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $）程序。在这种情况下，你只需要给程序本身授予exec，而不是给它触发的对象：
  .procedure TO用户;同样，如果你想在特定模式的所有过程中允许exec，你可以这样说：
 p> 
 
 
  GRANT EXEC ON SCHEMA :: dbo TO user; 
  
一个例外是当您的存储过程编译动态SQL时。在这些情况下，您可能仍需要在动态SQL执行的上下文中对基础表应用权限，或者您可以使用 EXECUTE AS OWNER 。
 
My application (C#, ASP.Net) needs to insert, update and delete data in the DB, and run stored procedures. I need to prevent it from modifying the DB schema - no altering tables, creating or dropping, no changes to stored procedures. 

What permissions combination do I need to grant to the application user?  Just 'select' isn't going to work, because it needs to insert/update/delete data in tables. 

How do I check permissions and access for a particular login? 
How do I grant or deny permissions and access for a login? 
I need to give permissions to a new user (login) to access only one database. 

Using SQL Server 2008 R2, with SSMS. 
解决方案
If you really want to control this at the object level, you can do:
GRANT SELECT,UPDATE,INSERT,DELETE ON dbo.table TO user;
At the schema level:
GRANT SELECT,UPDATE,INSERT,DELETE ON SCHEMA::dbo TO user;
Ideally, though, you would not allow ad hoc DML against your tables, and control all DML through stored procedures. In which case you just need to grant exec on the procedure itself, and not to the objects it touches:
GRANT EXEC ON dbo.procedure TO user;
Similarly if you want to allow exec on all procedures in a specific schema, you can say:
GRANT EXEC ON SCHEMA::dbo TO user;
The one exception is when your stored procedure composes dynamic SQL. In those cases you might still need to apply permissions to the underlying tables in the context of the dynamic SQL execution, or you may be able to use EXECUTE AS OWNER.

                        
这篇关于如何允许SQL Server用户插入/更新/删除数据，但不改变模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！