116

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.

Nick
  • 1,299
  • 4
  • 13
  • 10

5 Answers5

127

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.

Sanjeevakumar Hiremath
  • 10,299
  • 3
  • 38
  • 46
  • I just tried this on SQL Server 2008 Standard (amazon RDS) and it worked like a charm. – datagod Mar 05 '13 at 06:04
  • could you please provide an example? lets say i need to grant EXECUTE permissions on all SP's for the user SPExecuter – Uri Abramson Apr 25 '13 at 08:59
  • 4
    the only other statement needed is the line adding the user to the role, like so: ALTER ROLE [abc] ADD MEMBER [user_name] – dhochee May 07 '13 at 17:03
  • 31
    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. – Chris Peacock Sep 24 '15 at 16:18
  • 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]; – lisandro Oct 05 '20 at 15:00
37

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

USE [DB]
GRANT EXEC TO [User_Name];
Bartosz X
  • 2,113
  • 22
  • 30
  • 1
    worked for me, and presumably covers all future stored procs (we'll find out), rather than scripts that name each stored proc. – Ken Forslund Nov 03 '18 at 20:53
19

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

Colin
  • 21,132
  • 15
  • 96
  • 179
6

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
Junior Mayhé
  • 15,301
  • 26
  • 105
  • 157
Hemanshu Trivedi
  • 61
  • 1
  • 1
1
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
slugster
  • 47,434
  • 13
  • 92
  • 138
GCH
  • 19
  • 2