Create User Defined db_executor database role

Fixed database roles in SQL Server include db_datareader, db_datawriter and other. These roles grant users read and write access to the database, allowing them to interact with tables. It’s worth noting, however, that there is no built-in role particularly designed to allow users authorization to execute stored procedures. Fortunately, this problem is readily solved by defining a new role to serve this purpose.

From SQL Server 2012 and in higher version we have the ability to create a User Defined Server Level Role. We can create a user database role and can be named as ‘db_executor’. To create a new database role named “db_executor” in SQL Server, you can use the following T-SQL script:

-- Use the target database where you want to create the role
USE [AdventureWorks2022]
GO

-- Create a new database role named "db_executor"
CREATE ROLE [db_executor]
GO

-- Grant the role permission to execute stored procedures
GRANT EXECUTE TO [db_executor]
GO

Replace “YourDatabaseName” with the name of the database where you want the role to be created. This script will establish a new role called “db_executor” and allow it access to the specified database’s stored procedures.

To add a login to an existing database role in SQL Server, you can use the sp_addrolemember stored procedure. Here’s an example of how to do it:

USE [YourDatabaseName]
GO

-- Add a login to a database role
EXEC sp_addrolemember 'db_executor', 'YourLoginName'
GO

Replace “YourDatabaseName” with the name of your target database, and “YourLoginName” with the login name you want to include in the role. This script will assign the supplied login to the “db_executor” database role, allowing it the role’s permissions.

Or,

To add a login to a database role in SQL Server, use the ALTER ROLE statement. Here’s an example:

USE [YourDatabaseName]
GO

-- Add a login to the existing database role
ALTER ROLE [db_executor] ADD MEMBER [YourLoginName]
GO

Replace “YourDatabaseName” with the name of your target database, and “db_executor” with the name of the database role to which the login should be added. Replace “YourLoginName” with the name of the login you want to add to the role as well. This script will add the supplied login to the existing database role, allowing it the role’s permissions.