Grant execute permission for a user on all stored procedures in database?

Question

I generated a script from the old database, created a new database and imported all data from the old database. So far so good, however, no user has executed rights for stored procedures. I know I can use

GRANT EXECUTE ON [storedProcName] TO [userName] 

If it was just a few procedures, however, I have about 100 so what's the easiest way for me to grant execute access for a specific user to all of them?

Thanks in advance.

Answer 1

Create a role add this role to users, and then you can grant execute to all the routines in one shot to this role like this:

CREATE ROLE <abc>

GRANT EXECUTE TO <abc>

EDIT

The above code will work in SQL Server 2005, I'm not sure about the backward compatibility of this feature, I'm sure anything later than 2005 should be fine.