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

By: Pablo Echeverria   |   Updated: 2019-10-17   |   Comments   |   Related: > Security

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

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips