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

Question

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?

Youthful

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.

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.

Lamented

I just tried this on SQL Server 2008 Standard (amazon RDS) and it worked like a charm. – Hunsaker

could you please provide an example? lets say i need to grant EXECUTE permissions on all SP's for the user SPExecuter – Macrography

the only other statement needed is the line adding the user to the role, like so: ALTER ROLE [abc] ADD MEMBER [user_name] – Sewerage

You don't actually need to create a role, you may apply this directly to a user, e.g. GRANT EXECUTE TO userName. I think this is sufficient for the OP's question. – Mother

The question was how to give one user permission, not how to give a role permission, I thing the correct answer is Bartosz X's. GRANT EXEC TO [User_Name]; – Britannic

Answer 2

Without over-complicating the problem, to grant the EXECUTE on chosen database:

USE [DB]
GRANT EXEC TO [User_Name];

Truax

worked for me, and presumably covers all future stored procs (we'll find out), rather than scripts that name each stored proc. – Kramlich

Answer 3

This is a solution that means that as you add new stored procedures to the schema, users can execute them without having to call grant execute on the new stored procedure:

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'asp_net')
DROP USER asp_net
GO

IF  EXISTS (SELECT * FROM sys.database_principals 
WHERE name = N'db_execproc' AND type = 'R')
DROP ROLE [db_execproc]
GO

--Create a database role....
CREATE ROLE [db_execproc] AUTHORIZATION [dbo]
GO

--...with EXECUTE permission at the schema level...
GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;
GO

--http://www.patrickkeisler.com/2012/10/grant-execute-permission-on-all-stored.html
--Any stored procedures that are created in the dbo schema can be 
--executed by users who are members of the db_execproc database role

--...add a user e.g. for the NETWORK SERVICE login that asp.net uses
CREATE USER asp_net 
FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] 
WITH DEFAULT_SCHEMA=[dbo]
GO

--...and add them to the roles you need
EXEC sp_addrolemember N'db_execproc', 'asp_net';
EXEC sp_addrolemember N'db_datareader', 'asp_net';
EXEC sp_addrolemember N'db_datawriter', 'asp_net';
GO

Reference: Grant Execute Permission on All Stored Procedures

Tusk

Answer 4

use below code , change proper database name and user name and then take that output and execute in SSMS. FOR SQL 2005 ABOVE

USE <database_name> 
select 'GRANT EXECUTE ON ['+name+'] TO [userName]  '  
from sys.objects  
where type ='P' 
and is_ms_shipped = 0  

Stearns

You need to include also 'PC' type to include CLR stored procedures. – Alkalize

Answer 5

USE [DATABASE]

DECLARE @USERNAME VARCHAR(500)

DECLARE @STRSQL NVARCHAR(MAX)

SET @USERNAME='[USERNAME] '
SET @STRSQL=''

select @STRSQL+=CHAR(13)+'GRANT EXECUTE ON ['+ s.name+'].['+obj.name+'] TO'+@USERNAME+';'
from
    sys.all_objects as obj
inner join
    sys.schemas s ON obj.schema_id = s.schema_id
where obj.type in ('P','V','FK')
AND s.NAME NOT IN ('SYS','INFORMATION_SCHEMA')


EXEC SP_EXECUTESQL @STRSQL

Hooke