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

sql sql-server sql-server-2000
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.

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More
How to get the first and last date of the current year?

Using SQL Server 2000, how can I get the first and last date of the current year? Expected Output: 01/01/2012 and 31/12/2012

Read More