Role de ejecución para todos los stored procedures

Una situación bastante común es querer que un usuario (o grupo de usuarios) tenga permiso de ejecución para todos los stored procedures de una base. El SQL Server nos brinda los roles db_datareader, db_datawriter u otros, pero ninguno da solamente esos permisos. Para poder hacer esto con roles es necesario asignarlo al role db_owner, lo cual da muchos mas permisos de los deseados.
La única forma es dar los permisos de cada stored de forma individual, lo cual si tenemos muchos puede ser realmente molesto.
La solución que planteo es hacer un grupo al cual llamé db_executeall. Armé un script que recorre todas las bases y en todas crea dicho role.
El segundo paso es crear un cursor que recorra todos los stored procedures y asigne los permisos al role. El código estaría estructurado de la siguiente forma:

Cursor que recorre bases
begin
Crear role db_executeall si no existe
Cursor que recorre procedures
begin
Asignar permiso de ejecución al role
end
end

Esto nos dejaría en cada base un role nuevo que tiene todos los permisos de ejecución así solamente debemos asignarlo ahí al usuario. Una idea extra es agregar este script en un job diario, o por hora, para asegurarnos que nuestro role siempre esté actualizado con todos los permisos.
Si alguién necesita el código puede escribirme.
Saludos!

Defaul role como en Oracle

Una cosa que mucha gente que trabaja con SQL Server extraña de Oracle es lo conocidos como Default Roles.
¿En que consiste esto? Consiste en tener un conjunto de permisos al comenzar la sesion y en tiempo de ejecución poder acceder a otros permisos de forma explicita. Un ejemplo util sería darle a un usuario permisos de DATAREADER con la opción de ascender a DATAWRITER. De esta forma si la mayoria de sus tareas consisten en solo leer datos no podría modificar cosas por error salvo que explicitamente suba sus permisos.
Para implementar esto haremos lo siguiente. Para empezar crearemos en la master (o en alguna base de DBA) una tabla que tenga la siguiente estructura:

T_Permisos(Usuario SYSNAME, Role SYSNAME, Defualt TINYINT)

Con esta tabla indicaremos los roles que tiene permitidos un usuario. La primer columna sera el nombre del LOGIN, la segunda el role y la última si este role lo tiene desde el comienzo o lo debe explicitar.
El segundo paso es generar un logon trigger. Este trigger lo que hará será leer la tabla con un filtro en el where de Default=1 y Usuario=ORIGINAL_LOGIN() y hacer con SQL Dinamico una asignación de permisos a los roles indicados y quitandole cualquier otro que pudiera tener.
Con esto ya tenemos garantizado que al momento de conectarse tendrá un subconjunto de permisos.
El último paso es permitir la escalada de permisos, lo cual lo haremos mediante un stored procedure. El stored GrantRoles(Role SYSNAME) deberán tener permiso de ejecución todos los miembros de public. Este stored lo que hará es un select para controlar si el usuario logueado tiene en la tabla registrado el rol que solicita, en caso negativo no se hace nada y en caso afirmativo se asigna de forma dinámica el role. Es importante que este stored utilice la sentencia EXECUTE AS SELF por ejemplo y se haya creado con un usuario administrador. La idea es que el no tenga los permisos pero el stored si, y con la lógica adecuada se los asigne.
Una vez terminada su tarea, cuando se desconecte mantendrá los permisos, pero al establecer una nueva conexion los perderá.
Es interesante el tema de conexiones simultaneas, ahí se pueden analizar varias variantes como controlar en la sys.dm_exec_sessions para ver de solo sacar los permisos si no hay otra conexion viva del mismo usuario.
Saludos!!