Como administrador de base de datos o desarrollador, es posible que te encuentres en situaciones en las que necesites encontrar todos los lugares donde se está utilizando un inicio de sesión o usuario específico en tu instancia de SQL Server. Esto es particularmente importante cuando la contraseña del inicio de sesión o usuario está a punto de cambiar, ya que debes actualizar todas las configuraciones relevantes para garantizar un acceso ininterrumpido a la base de datos.

En este artículo, discutiremos un script que se puede utilizar para identificar rápidamente dónde existe un inicio de sesión o usuario en varios componentes de SQL Server, como servidores vinculados, trabajos de SQL Agent e información de SSIS almacenada en la base de datos SSISDB.

Encontrar servidores vinculados

La primera parte del script busca el inicio de sesión o usuario dentro de las configuraciones de servidores vinculados en la base de datos master. Devuelve el nombre del servidor vinculado, la base de datos asociada y el nombre de usuario. Esta información puede ayudarte a identificar los servidores vinculados que necesitan ser actualizados con la nueva contraseña.

Encontrar trabajos de SQL Agent

La segunda parte del script busca el inicio de sesión o usuario dentro de los trabajos de SQL Agent en la base de datos msdb. Utiliza el operador LIKE para encontrar registros donde tanto la base de datos como el nombre de usuario están presentes en el comando del trabajo. Ten en cuenta que esta búsqueda puede devolver falsos positivos, por lo que es importante revisar los resultados cuidadosamente. El script devuelve el nombre del trabajo, el ID del paso, el nombre del paso y el comando que contiene la información de la base de datos y el usuario.

Encontrar información de SSIS

La tercera parte del script busca el inicio de sesión o usuario dentro de la información de SSIS almacenada en la base de datos SSISDB. Primero verifica si la base de datos SSISDB existe para evitar errores. Si la base de datos existe, busca el inicio de sesión o usuario dentro de las carpetas, proyectos, parámetros de objetos y variables de entorno. El script devuelve información como la carpeta, el proyecto, el paquete, el parámetro, el valor predeterminado de diseño, el valor predeterminado y cualquier trabajo de SQL Agent asociado y sus comandos.

Al ejecutar este script, puedes identificar rápidamente todos los lugares donde se está utilizando un inicio de sesión o usuario específico dentro de tu instancia de SQL Server. Esta información te ayudará a actualizar las configuraciones necesarias y garantizar una transición sin problemas al cambiar la contraseña.

Recuerda siempre probar cualquier cambio en un entorno no productivo antes de implementarlo en un entorno de producción.

Aquí está el script:

DECLARE @Database VARCHAR(128), @Username VARCHAR(128)
SET @Database = 'TuBaseDeDatos' -- ingresa el valor aquí
SET @Username = 'TuUsuario' -- ingresa el valor aquí

-- encontrar dónde existe el usuario dentro de las configuraciones de servidores vinculados
USE [master]
SELECT [s].[name] [NombreServidorVinculado], [s].[data_source] [BaseDeDatos], [ll].[remote_name] [NombreUsuario]
FROM [sys].[servers] [s]
INNER JOIN [sys].[linked_logins] [ll] ON [ll].[server_id] = [s].[server_id]
WHERE [s].[data_source] = @Database
AND [ll].[remote_name] = @Username

-- encontrar dónde existe el usuario en los pasos de trabajo de SQL Agent
USE [msdb]
SELECT [j].[name] [NombreTrabajo], [js].[step_id], [js].[step_name], [js].[command]
FROM [dbo].[sysjobs] [j]
INNER JOIN [dbo].[sysjobsteps] [js] ON [js].[job_id] = [j].[job_id]
WHERE [js].[command] LIKE '%'+@Database+'%'
AND [js].[command] LIKE '%'+@Username+'%'

-- encontrar dónde existe el usuario para la información de SSIS almacenada en la base de datos SSISDB
IF DB_ID('SSISDB') IS NOT NULL
BEGIN
  SELECT [f].[name] [carpeta], [p].[name] [proyecto], [op].[object_name] [paquete], [op].[parameter_name] [parámetro],
         [op].[design_default_value], [op].[default_value], [j].[name] [NombreTrabajo], [js].[step_id], [js].[step_name],
         [js].[command]
  FROM [SSISDB].[catalog].[folders] [f]
  INNER JOIN [SSISDB].[catalog].[projects] [p] ON [p].[folder_id] = [f].[folder_id]
  INNER JOIN [SSISDB].[catalog].[object_parameters] [op] ON [op].[project_id] = [p].[project_id]
  LEFT JOIN [msdb].[dbo].[sysjobsteps] [js] ON [js].[command] LIKE '%'+[op].[object_name]+'%'
  LEFT JOIN [msdb].[dbo].[sysjobs] [j] ON [j].[job_id] = [js].[job_id]
  WHERE [op].[design_default_value] = @Database
  OR [op].[design_default_value] = @Username
  OR [op].[default_value] = @Database
  OR [op].[default_value] = @Username

  SELECT [e].[name] [Entorno], [ev].[name] [Variable], [ev].[value]
  FROM [SSISDB].[catalog].[environments] [e]
  INNER JOIN [SSISDB].[catalog].[environment_variables] [ev] ON [ev].[environment_id] = [e].[environment_id]
  WHERE [ev].[value] = @Database
  OR [ev].[value] = @Username
END
    

Recuerda reemplazar ‘TuBaseDeDatos’ y ‘TuUsuario’ con el nombre real de la base de datos y el inicio de sesión o nombre de usuario que deseas buscar.

Al utilizar este script, puedes encontrar fácilmente todos los lugares donde se está utilizando un inicio de sesión o usuario específico dentro de tu instancia de SQL Server. Esto te ayudará a garantizar que todas las configuraciones necesarias se actualicen al cambiar la contraseña, evitando posibles interrupciones en el acceso a tu base de datos.