MS Access and Sql Server Permissions

[gem1204]

I have created an adp, and have created stored procedures for all of my forms. In the SQL Server database I have given my users permissions as db_datareader and db_datawriter, however they were unable to open any of my forms because they didn’t have execute permissions on my stored procedures. I finally had to give them permissions as db_owner so they could use my forms. Do I have to give them db_owner permissions to execute my stored procedures? I know I can go in and assign them execute permissions for my stored procedures, but there has to be a better way. Does anyone know how to give users execute permissions on stored procedures without having to give them db_owner permissions or having to grant execute permissions for each stored procedure individually?

I am still in the development phase of my project and having to assign users or groups with each stored procedure I create could really be a pain. Can someone please help me?

 

[tehNellie]

Granting permission on all your stored procedures in one go is a simple task, you can query Sysobjects or information_schema to get your stored procedure names and assign permissions to them via t-sql in one fell swoop.

There are a couple of examples using sysobjects of how to do this:

http://www.sql-server-performance.com/bl_security_sp.asp

 

[tehNellie]

I guess just to add, that if you are going to have multiple users, it might be best to create a new Role called "runSP" or something, assign the Exec permission on your stored procedures to this role then you can just add all future new users to this role and they'll have permission to run the stored procedure without requiring you to change your permissions every time you add or remove users.