Skip to main content

Creating a db_executor Database Role for use with Tricerat database

SQL Server includes built-in fixed database roles like db_datareader and db_datawriter, but lacks a corresponding db_executor role for stored procedure execution permissions. This guide shows how to create a custom db_executor role to grant execute permissions on all stored procedures in a database.

Overview​

The db_executor role allows you to grant execute permissions on all stored procedures without making users members of more permissive roles like db_owner. This follows the principle of least privilege by granting only the necessary permissions.

Benefits​

  • Simplified Permission Management: Grant execute permissions to multiple users through a single role
  • Principle of Least Privilege: Users get only execute permissions without broader access
  • Consistency: Standardizes permission management across databases
  • Maintainability: Easier to manage than individual grants per user

Creating the db_executor Role​

Run the following SQL script in SQL Management Studio on your database:

-- Create the database role
CREATE ROLE db_executor

-- Grant execute permissions to the role
GRANT EXECUTE TO db_executor

Adding Users to the Role​

Once the role is created, add users or other database principals:

-- Add a specific user to the role
ALTER ROLE db_executor ADD MEMBER [username]

-- Add a Windows group
ALTER ROLE db_executor ADD MEMBER [DOMAIN\GroupName]

-- Add another database role
ALTER ROLE db_executor ADD MEMBER [another_role]
info

You can also use the SQL management UI to add this role to user accounts.

Verifying Role Membership​

To check which users are members of the db_executor role:

SELECT 
    r.name AS RoleName,
    m.name AS MemberName,
    m.type_desc AS MemberType
FROM sys.database_role_members AS drm
INNER JOIN sys.database_principals AS r 
    ON drm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals AS m 
    ON drm.member_principal_id = m.principal_id
WHERE r.name = 'db_executor'
ORDER BY m.name

Removing Users from the Role​

To remove a user from the role:

ALTER ROLE db_executor DROP MEMBER [username]

Use Cases for ScrewDrivers​

The db_executor role is particularly useful in ScrewDrivers deployments when:

  • Application service accounts need to execute stored procedures but shouldn't have write access to tables
  • You want to separate read, write, and execute permissions for different service accounts
  • Implementing security best practices for database access
  • Multiple components need execute permissions without elevated privileges

Additional Considerations​

Schema-Specific Permissions​

If you need more granular control, you can grant execute permissions on specific schemas:

-- Create role for specific schema
CREATE ROLE schema_executor

-- Grant execute on specific schema only
GRANT EXECUTE ON SCHEMA::dbo TO schema_executor

Combining with Other Roles​

Users can be members of multiple roles. For example, a service account might need both read and execute permissions:

ALTER ROLE db_datareader ADD MEMBER [service_account]
ALTER ROLE db_executor ADD MEMBER [service_account]

Security Notes​

warning

The db_executor role grants execute permissions on all stored procedures in the database, including system stored procedures. Ensure this level of access is appropriate for your security requirements.

info

Document all custom roles in your database deployment scripts and maintain them in version control alongside your application code.