Granting execute permissions to a database login permanently using db_executor role

I often find that some logins will need execute permissions to objects on the database, in some cases a development environment might exists where users dont have GUI interface to interact with the database, i.e every operation is all done through stored procedures. In such cases, it might be acceptable to grant execute permissions to all objects on the database.  

In SQL server the role db_datareader has always existed, but how come we don’t have a role called db_executor. In this blog, I will show you how you can create a custom role which gives execute permissions on stored procedures, functions on the database.  

For the purpose of demonstration I run the following scripts with SA privileges on the database  

--Create a database and a login named test, grant the test login read only access to the DEMO database
CREATE DATABASE [DEMO] ON  PRIMARY
( NAME = N'DEMO', FILENAME = N'C:\Database\DEMO.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'DEMO_log', FILENAME = N'C:\Database\DEMO_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DEMO]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [DEMO]
GO
EXEC sp_addrolemember N'db_datareader', N'test'
GO

Whilst still logged in as myself, create objects under the DEMO database. 
 

use DEMO
GO
create table tblUser
(
UserID int identity(1,1),
UserName varchar(30)
)

insert into tblUser (UserName)
	Select 'Tom'
union
	Select 'Jen'

GO

Create procedure spGetUserName
as
select UserID, UserName
from tblUser

GO
exec spGetUserName

Now log on as the test user to the DEMO database and run the following scripts.  

 
select * from tblUser 
1 Tom
2 Jen 

Try executing the stored procedure we get the error below 
 
Msg 229, Level 14, State 5, Procedure spGetUserName, Line 1 
The EXECUTE permission was denied on the object ‘spGetUserName’, database ‘DEMO’, schema ‘dbo’. 
 
I can either grant execute permissions on this single stored procedure, but imagine that we had over a thousand stored procedures. One easy way to do this is to create a role and grant execute permission on the actual schema itself. 

use DEMO
go
CREATE ROLE [db_executor]
GO
GRANT EXECUTE ON SCHEMA ::dbo TO db_executor
GO
--Then we’ll add the Test user in the database to the db_executor role in the database:
USE DEMO
GO
EXEC sp_addrolemember N'db_executor', N'test'
GO

After running the above, we now execute the stored procedure and it executes fine without any problems. 

execute spGetUserName 

1 Tom
2 Jen