mssqltips logo

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

By:   |   Updated: 2019-10-17   |   Comments   |   Related: More > 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 '%'[email protected]+'%'
  AND [js].[command] LIKE '%'[email protected]+'%'

-- 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:

LinkedServerName Database Username
DBLINK YourDatabase YourUser

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:

JobName step_id step_name command
ScheduledJob 1 LoadData /FILE "\"\\localhost\packages\package1.dtsx\"" /CONNECTION LINKDB;"\"Data Source=YourDatabase;User ID=YourUser;Password=YourPassword;Provider=DBProvider;Persist Security Info=True;\"" /X86  /CHECKPOINTING OFF /REPORTING E

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:

folder project package parameter design_default_value default_value
YourFolder YourSolution package2.dtsx DBName AnotherDatabase YourDatabase
YourFolder YourSolution package2.dtsx UserName YourUser NULL
JobName step_id step_name command    
ScheduledJob 1 LoadData /ISSERVER "\"\SSISDB\YourSolution\package2.dtsx\"" /SERVER localhost /Par "\"Password\"";YourPassword; /CALLERINFO SQLAGENT /REPORTING E    
ScheduledJob 1 LoadData /ISSERVER "\"\SSISDB\YourSolution\package2.dtsx\"" /SERVER localhost /Par "\"Password\"";YourPassword; /CALLERINFO SQLAGENT /REPORTING E    

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:

Environment Variable value
YourEnv DBName YourDatabase
YourEnv UserName YourUser
Next Steps


Last Updated: 2019-10-17


get scripts

next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implement new processes, created better monitoring tools and grown my data scientist skills.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools