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:
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
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-10-17