Need help figuring out correct permissions for SQL Server Users

Posted on

Question :

In most of my databases I create, I need to give permissions to do the following:

  1. SELECT/INSERT/UPDATE/DELETE on all tables in the database
  2. Execute all (user) stored procedures in the database

Will giving users datareader and datawriter be enough for the sproc’s or what other roles/permissions do I need to give?

If I need to do more than just datareader and datawriter, could you point me in the right direction to creating a script to affect all users in the database.

Thanks in advance

Answer :

With (1) you should be good giving them db_datareader and db_datawriter.

On (2) I would probably create a role and then grant that role execute permissions on all the stored procedures, in place of granting it to each individual user. Then just add those users to the role. If you have all the stored procedures under a schema name you should be able to grant execute to that schema. That will save time and not have to worry about new procedures being added to the database.

In addition to Shawn’s answer and your comment..

Tables that are maintained by stored procedures or views only do not require any explicit permissions. “Ownership chaining2 states they are not checked: this includes a DENY.

I do prefer schemas though but I also disallow direct table access. For a given schema (Desktop, WebGUI etc) then I assume all clients with permissions need to use it. Within that, I’d have some application security too though eg based on SUSER_SNAME() or such to track users.

Leave a Reply

Your email address will not be published. Required fields are marked *