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

Nick picture Nick · Mar 25, 2011 · Viewed 311.6k times · Source

I generated script from old database, created a new database and imported all data from old database. So far so good, however, no user has execute 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

Sanjeevakumar Hiremath picture Sanjeevakumar Hiremath · Mar 25, 2011

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

CREATE ROLE <abc>
GRANT EXECUTE TO <abc>

EDIT
This works in SQL Server 2005, I'm not sure about backward compatibility of this feature, I'm sure anything later than 2005 should be fine.