Find Embedded SQL Server Logins in Jobs, Linked Servers or SSISDB

Problem

A database login or user (SQL Login, Oracle User, etc.) is actively being used and embedded in code, but the password is about to change. How can you find all of the places where it exists with SQL Server such as Jobs, Linked Servers and SSIS information stored on the server?

Solution

The below script can be used to find where a particular login/user exists on a SQL Server instance.  This can be used to quickly identify where you may have configurations settings that may need to changed.

Script

DECLARE @Database VARCHAR(128), @Username VARCHAR(128)
SET @Database = 'YourDatabase' -- enter value here
SET @Username = 'YourUser' -- enter value here
-- find where user exists within linked server configurations
USE [master]
SELECT [s].[name] [LinkedServerName], [s].[data_source] [Database], [ll].[remote_name] [Username]
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
-- find where user exists with SQL Agent job steps
USE [msdb]
SELECT [j].[name] [JobName], [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+'%'
-- find where user exists for SSIS info stored in SSISDB database
IF DB_ID('SSISDB') IS NOT NULL
BEGIN
  SELECT [f].[name] [folder], [p].[name] [project], [op].[object_name] [package], [op].[parameter_name] [parameter],
         [op].[design_default_value], [op].[default_value], [j].[name] [JobName], [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] [Environment], [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

Below is information about what is output from the script.

Linked Servers

The first part of the script searches the linked servers in the master database. Only if both the database and user name match, then linked server is listed.

The results are similar to this:

SQL Server Agent Jobs

The second part of the script searches in the SQL Server Agent jobs in the msdb database. Note that you may get false positives because the search is performed with the LIKE operator, but it returns records where both the database and user name are found.

The results are similar to this:

SSIS Jobs

The third part of the script searches the SSIS jobs, but only if the SSISDB database exists preventing errors from being generated.

Note that there is a separate parameter for the database and another for the user, so you need to check for the existence of both parameters, and then you will need to update another parameter (usually the password) that contains the password (both design_default_value and default_value). If there is a SQL Agent Job that uses the package, it is also listed along with its command, so you can see if the password needs to be changed as well.

The results are similar to this:

SSIS Environment Variables

The last part of the script searches the SSIS environment variables, again only if the SSISDB database exists preventing errors from being generated.

Again, there is a separate parameter for the database and another for the user, so you need to check for the existence of both parameters, and then you will need to update another parameter (usually named password) that contains the password. You won’t know if the environment variables are being used in a package, so it’s better to change it to be safe. The results are similar to this:

Next Steps

• Check out my other tips about scripting

Pablo Echeverria

Pablo Echeverria has worked for more than 10 years as a software programmer and analyst, during which time I studied parallel programming and became a senior programmer specialist. Afterward, he switched to a DBA position implementing new processes and creating better monitoring tools, while growing his data scientist skills to improve my customer’s businesses.  Check out Pablo’s most recent book, “Hands-on Data Virtualization with Polybase“. This book brings exciting coverage on establishing and managing data virtualization using Polybase. It teaches how to configure Polybase on almost all relational and nonrelational databases, to setup a test environment for any tool or software instantly without any hassle, and to rapidly design and build high performing data warehousing solutions.

• MSSQLTips Awards: Rising Star (50+ tips) – 2024 | Author Contender – 2018, 2022, 2023 | Rookie Contender – 2017