Microsoft SQL Server

Microsoft SQL Server

Descripción

Dudas técnicas, db scripts y consultas de administración y desarrollo sobre bases de datos <strong>Microsoft SQL Server</strong>

Crear en SQL Server un rol adicional para ejecutar stored procedures

Cómo crear un rol en SQL Server para poder dar a los usuarios fácilmente permisos para ejecutar stored procedures.

En SQL Server no existe un rol predefinido para que un usuario pueda ejecutar stored procedures, aparte del db_owner.

Si se trabaja con stored procedures y se quiere permitir a usuarios que no tengan porqué ser owners de una base de datos ejecutar procedimientos almacenados de la misma, se puede crear un rol específico con un grant de permisos de ejecución 'EXECUTE'.

Así después sólo hay que asignar ese rol a los usuarios que tengan que ejecutar stored procedures de una base de datos.

 

Es tan fácil como hacer algo así:

-- Crea un nuevo rol para ejecutar stored procedures
CREATE ROLE db_execute

-- Asigna permisos de ejecución de procedimientos almacenados al rol
GRANT EXECUTE TO db_execute

-- Agrega un usuario al recién creado rol db_execute role
EXEC sp_addrolemember 'db_execute', 'usuario'

Después de esto 'usuario' ya podrá ejecutar stored procedures de la base de datos en la que se ha creado el rol aunque no sea 'db_owner' de esa base de datos..

 

SQL Dinámico con retorno de valores en procedimientos de SQL Server

Si necesitas parametrizar algo más que los valores de los campos que se introducen en la parte del WHERE en una sentencia T-SQL de un procedimiento almacenado seguramente vas a tener que utilizar SQL dinámico, aunque si se puede hacer lo mismo sin SQL dinámico siempre es mejor, más seguro y más eficiente.

Ejemplo de sentencias que se pueden hacer dentro de un procedure con SQL 'Estático'

INSERT INTO estadisticas(idFecha,fecha,bd,tabla,campo,filtro,funcion,valor) 
            values (@idfecha,getdate(),@bd,@tabla,@campo,'','COUNT',@valor)

SELECT @valor= valor FROM estadisticas WHERE bd=@bd and tabla=@tabla and campo=@campo

Ejemplos de sentencias que se pueden hacer dentro de un procedure con SQL dinámico

Si es necesario parametrizar el nombre de la tabla de la query, o partes de la sentencia fuera del WHERE, se puede utilizar SQL dinamico con la opción poco recomendada porque el motor no puede optimizar nada, y porque se corre el peligro de sufrir un ataque de SQL Injection de montar la sentencia en un string, y ejecutarlo directamente con EXEC.

Un ejemplo de lo que se puede hacer, pero es mejor evitar: