El otro día en un grupo de Facebook un participante hizo una pregunta relacionada a un caso en donde tenia un Procedimiento Almacenado con SQL Dinámico y que al ejecutarlo le daba errores de permisos sobre los objetos que usaba ese procedimiento.
Esto técnicamente se da porque el SQL Dinámico pasa las credenciales del contexto de ejecución del SP el cual es Caller por defecto, para mas información puedes acceder al siguiente enlace
En este post voy a hacer una demostración paso a paso de este caso y cual seria la forma de resolverlo.
Paso 1: Creamos los logins y usuarios en nuestra base de datos
En este primer paso vamos a crear dos (2) logins y usuarios a nuestra base de datos, uno de ellos con permisos restringidos y el otro con mas permisos.
USE AdventureWorks2019
GO
-- CREAMOS UN NUEVO LOGIN
CREATE LOGIN TRIGGERDB WITH PASSWORD ='PASSW@RD'
GO
CREATE LOGIN TRIGGERDB_FULL WITH PASSWORD ='PASSW@RD'
GO
-- CREAMOS EL USUARIO EN LA BASE DE DATOS
CREATE USER TRIGGERDB
GO
CREATE USER TRIGGERDB_FULL
GO
ALTER ROLE [db_owner] ADD MEMBER [TRIGGERDB_FULL] -- PERMISO DE DBOWNER
GO
Paso 2: Creación de Procedimientos almacenados
En este paso vamos a crear dos procedimientos almacenados , uno de ellos común y el otro usando SQL Dinámico
Luego al login TRIGGERDB le vamos a dar permisos de ejecución sobre los SP pero no así sobre las tablas
CREATE OR ALTER PROC DBO.USP_CUSTOMER AS
SELECT * FROM SALES.Customer
GO
-- CREAMOS OTRO STORE QUE CONSUME LA TABLA PERO CON SQLDINAMICO
CREATE OR ALTER PROC DBO.USP_CUSTOMER_DINAMICO AS
DECLARE @N NVARCHAR(50)
SET @N = N'SELECT * FROM SALES.Customer'
EXECUTE SP_EXECUTESQL @N
GO
-- TRIGGERDB SOLO TIENE PERMISOS A LOS STORES
GRANT EXECUTE ON DBO.USP_CUSTOMER TO TRIGGERDB
GRANT EXECUTE ON DBO.USP_CUSTOMER_DINAMICO TO TRIGGERDB
GO
Paso 3: Pruebas de funcionalidad
Ahora probaremos los dos procedimientos almacenados usando el login TRIGGERDB y además los permisos
EXECUTE AS LOGIN = 'TRIGGERDB'
SELECT SUSER_SNAME()
Hacemos un Select a la tabla con Triggerdb
SELECT * FROM SALES.Customer
Ejecutamos el primer procedimiento normal y vemos que funciona porque el login tiene permisos.
Msg 229, Level 14, State 5, Line 78 The SELECT permission was denied on the object ‘Customer’, database ‘AdventureWorks2019’, schema ‘Sales’.
EXEC DBO.USP_CUSTOMER
Ejecutamos el otro SP con SQL Dinámico sobre la misma tabla y falla por mas que tengamos permisos sobre el mismo , al ser SQL Dinámico se pasan las credenciales del Caller (TRIGGERDB) al objeto que usa el SP_executeSQL y como este login no tiene permisos sobre dicha tabla va a dar error
EXEC DBO.USP_CUSTOMER_DINAMICO
Msg 229, Level 14, State 5, Line 78 The SELECT permission was denied on the object ‘Customer’, database ‘AdventureWorks2019’, schema ‘Sales’.
REVERT
Paso 4: Resolviendo el problema
Para poder resolver estos problemas sin la necesidad de darle permisos al Login TRIGGERDB sobre la tabla al usar SQL Dinámico lo que debemos hacer es cambiar el contexto de ejecución del SP de caller a Owner o Login , yo lo voy a cambiar indicándole un login que si tiene permisos sobre la tabla por mas que luego lo llame otro que no tiene permisos.
ALTER PROC DBO.USP_CUSTOMER_DINAMICO
WITH EXECUTE AS 'TRIGGERDB_FULL' -- CAMBIO DE CONTEXTO A OWNER
AS
DECLARE @N NVARCHAR(50)
SET @N = N'SELECT * FROM SALES.Customer'
EXECUTE SP_EXECUTESQL @N
Ahora solo nos toca volver a probar (correr el código por partes)
EXECUTE AS LOGIN = 'TRIGGERDB'
SELECT SUSER_SNAME()
SELECT * FROM SALES.Customer -- FALLA PORQUE NO TENEMOS PERMISO
EXEC DBO.USP_CUSTOMER -- FUNCIONA
EXEC DBO.USP_CUSTOMER_DINAMICO -- FUNCIONA
REVERT
Conclusiones
Si usamos SQL Dinámico y nuestro user que llama al SP no tiene permisos sobre los objetos del mismo y no queremos darlos, entonces lo que debemos hacer es cambiar el contexto de ejecución de ese SP, Función o Trigger
Si tienes ganas de probar el nuevo SQL Server 2022 (aun esta en versión de Beta) sin la necesidad de hacer toda una instalación (podes revisar mi post de cómo instalar SQL Server 2022 paso a paso) lo que puedes usar es contenedores con Docker Desktop
Los pasos para esto van a ser:
Descargar e instalar el Docker Desktop si aun no lo tienes , eso lo puede hacer desde el siguiente enlace
Desde la consola de PowerShell bajar la imagen de SQL 2022 para linux usando el siguiente comando
Luego de bajar la imagen correspondiente solamente debemos crear nuestro contenedor con el siguiente código de ejemplo (yo aquí usare el puerto 15000 apuntado al 1433 de la imagen)
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Triggerdb!!!!" -p 15000:1433 -d mcr.microsoft.com/mssql/server:2022-latest
Por ultimo solo nos queda conectarnos con nuestro SSMS o Azure Data Studio a la imagen de SQL 2022 y ejecutar alguna consulta
También podes verificar la imagen y tu contenedor desde la consola de Docker Desktop
En el Microsoft BUILD realizado el pasado mes de mayo de 2022 se anuncio el primer preview publico (Beta) de la próxima versión de SQL Server (SQL Server 2022)
Para poder descargar dicha versión beta debes acceder al siguiente enlace
Para conectarte vas a necesitar el SSMS v 19 el cual también esta en beta y te recomiendo instalarlo en una maquina para pruebas, para poder descargar el SSMS lo podes hacer desde el siguiente link
En varias oportunidades podemos tener la necesidad de depurar alguna tabla grande, esta operación puede ser compleja y hasta impactar la performance de tu base de datos.
En el siguiente tutorial te muestro paso a paso cómo podes hacer un proceso para mover registros viejos a una tabla de historia afectando lo menos posible la performance de tu SQL.
Comparto también el código utilizado en dicho video tutorial
USE master
GO
DROP DATABASE DATOS
DROP DATABASE DATOS_HISTORY
CREATE DATABASE DATOS
CREATE DATABASE DATOS_HISTORY
USE DATOS
GO
-- CREAMOS UNA TABLA Y LA LLENAMOS CON 4M DE REGISTROS
SELECT TOP 4000000
H.AccountNumber,
h.BillToAddressID,
h.CustomerID,
h.OrderDate,
h.SalesOrderNumber,
h.TotalDue
INTO DBO.VENTAS FROM AdventureWorks2019.Sales.SalesOrderHeader H
CROSS JOIN AdventureWorks2019.sys.columns C
-- CREAMOS A LA BASE HISTORY LA TABLA VACIA
USE DATOS_HISTORY
GO
SELECT TOP 0 * INTO DBO.VENTAS FROM DATOS.DBO.VENTAS
USE DATOS
GO
-- CALCULAMOS LAS ORDENES MENORES A 2007
SELECT COUNT(1)
FROM DBO.VENTAS
WHERE OrderDate >= '20130101' and OrderDate < '20140101'
SELECT COUNT(1)
FROM DBO.VENTAS
use DATOS
DBCC TRACEON (1224) -- NO ESCALAN LOS LOKEOS PARA ESTA SESION
WHILE 1 = 1
BEGIN
DELETE TOP (50000) FROM DBO.VENTAS
OUTPUT
Deleted.AccountNumber,
Deleted.BillToAddressID,
Deleted.CustomerID,
Deleted.OrderDate,
Deleted.SalesOrderNumber,
Deleted.TotalDue
INTO DATOS_HISTORY.dbo.VENTAS
WHERE OrderDate >= '20130101'
and OrderDate < '20140101'
IF @@ROWCOUNT = 0 BREAK;
END;
SELECT COUNT(1) FROM DBO.VENTAS
SELECT COUNT(1) FROM DATOS_HISTORY.DBO.VENTAS
En algunas oportunidades observamos que SQL no utiliza nuestros índices creados.
En el siguiente tutorial te explico cuales son las principales causas.
También te comparto los ejemplos utilizados:
USE AdventureWorks2019
GO
SP_HELPINDEX 'Sales.SalesOrderDetail'
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 897;
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID +1 = 898;
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 897;
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE upper(ProductID) = '897';
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 897;
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 870;
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WITH (INDEX=ix_salesorderdetail_productid)
WHERE ProductID = 870;
En este tutorial te voy a mostrar cómo podes usar la función de TSQL string_split() para poder pasar una lista de valores a tus procedimientos almacenados.
También comparto el código utilizado en la demostración
USE ADVENTUREWORKS2019
GO
SELECT * FROM STRING_SPLIT('HOLA,MUNDO',',');
USE [MASTER]
GO
ALTER DATABASE [ADVENTUREWORKS2019] SET COMPATIBILITY_LEVEL = 120
GO
USE ADVENTUREWORKS2019
GO
SELECT * FROM STRING_SPLIT('HOLA,MUNDO',',');
ALTER DATABASE [ADVENTUREWORKS2019] SET COMPATIBILITY_LEVEL = 150
GO
CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_STRING
@VALORES VARCHAR(8000)
AS
SET NOCOUNT ON
SELECT * FROM SALES.SALESORDERDETAIL D
WHERE D.SALESORDERID
IN
(
SELECT VALUE FROM
STRING_SPLIT(@VALORES,',')
)
GO
--- USANDO FUNCIONES
CREATE OR ALTER FUNCTION [dbo].[SplitInts]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT Item = Item FROM
( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
FROM ( SELECT [XML] = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
WHERE Item IS NOT NULL
);
GO
CREATE OR ALTER FUNCTION [DBO].[SPLITSTRING]
(
@INPUT NVARCHAR(MAX),
@CHARACTER CHAR(1)
)
RETURNS @OUTPUT TABLE (
ITEM NVARCHAR(1000)
)
AS
BEGIN
DECLARE @STARTINDEX INT, @ENDINDEX INT
SET @STARTINDEX = 1
IF SUBSTRING(@INPUT, LEN(@INPUT) - 1, LEN(@INPUT)) <> @CHARACTER
BEGIN
SET @INPUT = @INPUT + @CHARACTER
END
WHILE CHARINDEX(@CHARACTER, @INPUT) > 0
BEGIN
SET @ENDINDEX = CHARINDEX(@CHARACTER, @INPUT)
INSERT INTO @OUTPUT(ITEM)
SELECT SUBSTRING(@INPUT, @STARTINDEX, @ENDINDEX - 1)
SET @INPUT = SUBSTRING(@INPUT, @ENDINDEX + 1, LEN(@INPUT))
END
RETURN
END
SELECT * FROM DBO.SPLITINTS( 'HOLA,MUNDO', ',')
SELECT * FROM DBO.SPLITSTRING( 'HOLA,MUNDO', ',')
CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_FUNCTION
@VALORES VARCHAR(8000)
AS
SET NOCOUNT ON
SELECT * FROM SALES.SALESORDERDETAIL D
WHERE D.SALESORDERID
IN
(
SELECT * FROM
DBO.SPLITINTS(@VALORES,',')
)
GO
CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_FUNCTION2
@VALORES VARCHAR(8000)
AS
SET NOCOUNT ON
SELECT * FROM SALES.SALESORDERDETAIL D
WHERE D.SALESORDERID
IN
(
SELECT * FROM
DBO.SPLITSTRING(@VALORES,',')
)
GO
-- PRUEBAS
EXEC DBO.USP_SPLIT_STRING
'43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'
EXEC DBO.USP_SPLIT_FUNCTION2
'43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'
EXEC DBO.USP_SPLIT_FUNCTION
'43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'
