Como norma general mi cabeza tiende a olvidar a asignar los permisos a los usuarios en los procedimientos almacenados, en mis años de programador siempre que se ponía algo en producción fallaba por los permisos que yo no asignaba (y creo que el resto tampoco), así que tocaba asignarlos uno a uno, una labor encantadora…
Indagando he encontrado como resolverlo y de una forma facil asignar permisos a todos los usuarios (Quizás haya mas formas de resolverlo, estaría encantado de oírlas):
1.- Crear un rol en Sql server database que se llame por ejemplo db_spexecutor, en el que asignamos los usuarios de sql server que queramos.
2.- En la pestaña de seguridad buscamos los procedimientos almacenados
3.- Podemos ir mas facil de 1 en 1 asignando permisos de ejecucion.
Pero si seguimos pensando que esto es una lata…
1.- Crear un rol en Sql server database que se llame por ejemplo db_spexecutor, en el que asignamos los usuarios de sql server que queramos.
2.- Crear el procedimiento almacenado:
CREATE PROCEDURE spGrantExectoAllStoredProcs @user sysname
AS
SET NOCOUNT ON
— 1 – Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
— 2 – Create temporary table
CREATE TABLE #StoredProcedures(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL, StoredProcName varchar(128) NOT NULL)
— 3 – Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME NOT LIKE ‘dt_%’ AND ROUTINE_TYPE = ‘PROCEDURE’
— 4 – Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM
#StoredProcedures
— 5 – WHILE loop
WHILE @MAXOID > 0
BEGIN
— 6 – Initialize the variables
SELECT @OwnerName = StoredProcOwner,@ObjectName = StoredProcName FROM StoredProcedures
WHERE OID = @MAXOID
— 7 – Build the string
SELECT @CMD1 = ‘GRANT EXEC ON ‘ + ‘[‘ + @OwnerName + ‘]’ + ‘.’ + ‘[‘ + @ObjectName + ‘]’ + ‘ TO ‘ +@user
— 8 – Execute the string
EXEC(@CMD1)
— 9 – Decrement @MAXOID
SET @MAXOID = @MAXOID – 1
END
— 10 – Drop the temporary table
DROP TABLE
#StoredProcedures
SET
NOCOUNT OFF
GO
Al ejecutarle nos pedira el usuario, le decimos el rol o un usuario en concreto y todos los permisos asignados de golpe.
Espero que os sirva.