One thing that comes up fairly frequently with SQL Server permissions is allowing execute permissions to all user created programmability on a database. This frequently needs to be done when migrating servers, allowing the user connecting to execute stored procedures. One way to do this is to set explicit permissions for all securables for each user. This is effective with a limited amount of stored procedures.

In doing so however, as the number of stored procedures increases, the feasibility of setting explicit permissions decreases. The above also requires that permissions be explicitly granted for newly created stored procedures.

Another way of doing so is to create a role (which we’ll call db_executor) and granting execute permissions to all programmability (or a well-defined subset if you prefer) to said role, then adding desired users to the role. Doing so looks like this:

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
EXEC sp_addrolemember 'db_executor', 'username'

The above:

1. Creates the role db_executor
2. Grants execute permissions to the role
3. Adds the desired user to the role

Because roles exist within a database, this should be done in the context of the database desiring the programmability required.