How to permit a SQL Server user to insert/update/delete data, but not alter schema?

Question

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.

Answer 1

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.