29

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.

jprusakova
  • 1,557
  • 3
  • 19
  • 31

1 Answers1

56

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Will granting 'update' and 'delete' permission allow that user alter and drop tables? Also, can I grant permissions on a schema, rather than per table? – jprusakova Jun 18 '12 at 16:36
  • 2
    No, update and delete refer to the data in the table, not the data itself. You can always try this. And yes I posted an example where you can affect the schema instead of per object (which can be helpful if you add tables later, permissions are inherited, unless you want to add a table that the user *doesn't* have access to, then you'll need to deny explicitly on that table). – Aaron Bertrand Jun 18 '12 at 16:38
  • how can I check and modify permissions for a login in SSMS or via script? which server role should I pick for this new user with limited access? – jprusakova Jun 18 '12 at 17:58
  • You can look at `sys.database_permissions`. I have no idea which server role you should use, but if you are granting explicit rights then public should be plenty (db_datareader / db_datawriter might be required depending on a bunch of other factors). Security is a complex topic and I think it is something where you might need a tutorial or a training session rather than responses to comments. – Aaron Bertrand Jun 18 '12 at 18:09
  • I don't know of any off-hand, sorry. I would search using google or bing, which you could also do. :-) – Aaron Bertrand Jun 18 '12 at 18:16
  • i am searching :) and reading, and trying to figure it out. Stackoverflow answers and comments help a lot, in addition to other stuff found online. – jprusakova Jun 18 '12 at 18:24
  • In my experience - if the procedure has EXECUTE AS CALLER, and the user has EXEC permission on the procedure, and the procedure does SELECT from some table, then the user still needs SELECT permission on that table. In my case the owner of the table is a different user to the owner of the procedure. I don't fully understand this, but does your claim only refer to cases where the same user owns the procedure and the table ? – M.M Mar 21 '23 at 01:31