Create Executer Role

Database fixed roles are predefined roles with specific permissions that allow their members to take actual roles in the organization. For example: members of the “db_backupoperator” fixed database role can back up the database.

The “db_datareader” and “db_datawriter” roles allow their members to read or write, respectively, all the tables in the database. Even if you create a new table in the future, these roles automatically have the relevant access permissions on the table.

What is missing is a “db_executer” role that has EXECUTE permissions on all the stored procedures and functions in the database. So here is a stored procedure that creates such a role and assigns to it all the relevant permissions. The stored procedure can assign permissions on all the relevant objects in a specific schema or on all the relevant objects in the whole database.

#roles #security