Sql-server – Need help figuring out correct permissions for SQL Server Users

permissionssql-server-2005

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

Best 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.

Related Question