Stored procedure execution permission for a db-role

Question

I would like to give to the role db_datareader the permission to execute a specific stored procedure.

Is this possible and if yes, how can I achieve this? I have tried to add the role in the permissions-tab of the stored procedure, but this seems not to work, the role is not accepted as a valid object.

Edit
Based on the answer from cainz, I have tried GRANT EXECUTE ON [SP_NAME] TO db_datareader, however the message was:

Msg 4617, Level 16, State 1, Line 1
Cannot grant, deny or revoke permissions to or from special roles.

It seems that it is not possible what I wanted to do.

Answer 1

You can't do what you want directly because you can't modify the permissions for system roles. This is a good thing: you don't want your system roles to have a different definition from everyone else's because patches and servicepacks could have side effects, third-party tools could break, new developers will be (unpleasantly) surprised etc.

You just need to manage the permissions in your database using a script or other tools. The obvious solution is simply to create your own role and put your database user(s) into both roles; this is easy to script and it's good practice to plan and document your security model explicitly anyway. Is there any specific reason why this wouldn't be suitable for your environment?

(Note: it's possible to add your own role into db_datareader, but the documentation specifically warns not to do that.)

Answer 2

This is documented on BOL/MSDN at http://msdn.microsoft.com/en-us/library/ms345484.aspx - note the box in yellow telling what you need to do if this is a system procedure.