db_datareader & Execute permissions on procs

I have a group of users that I have given db_datareader permissions to in an SQL Server 2000 database. I am also creating web pages on an intranet site that pulls data from the table. If I just use a select statement to pull the data from the table, the users dont have a problem. If I use a stored procedure with the exact same sql statement, the users get an error until I grant them execute permissions on the stored procedure.

I have heard that store procedures is the best way to handle data operations but having to make sure I assign execute permissions every time I create a stored procedures can be a pain. The only way I know of to make sure that they had permissions would be to make them a member of db_Owner which is definitely not an option.

Is this just the way it is, or is there some way to automatically grant them execute permissions on stored procedures that are nothing more than select statements and dont violate db_datareader permissions?I'm afraid that is the point of procs (from a security pov). You can allow execute on the sproc whilst not allowing them to access the underlying tables. Such granularity means you have to explicitly grant execute permissions on the stored procedures for the users to be able to use them.|||Hi there,

Yep as pootle says, you need to add exec permissions to SPs regardless of whether they have datareader or datawriter.
If you're not doing it already I would set up and NT group or custom database role then you wont need to add the permissions to each user all the time, just to the group or groups|||Alternatively, if you are using SQL 2005, you can grant SCHEMA permissions to a user or role.

grant execute on schema ::dbo to [public]

However, more granular permissions are considered at least a better, if not best, practice.|||thanks to all of you for your replies.sql