Snippet Name: SQL_Procedure_Creation_Help
Tags:
Created Date: 2025-06-16 13:12:46
Last Modified Date: 2025-05-31 10:31:48
Dependencies:
DBA Notes:
Sample Output:
Title: SQL Procedure Creation Help
Author: user
do you have a sql procedure named sp_RemoveViewsWithMaskingDependancies
Author: ChatGPT
I don't have a stored procedure with that exact name, but I can help you create one if you'd like! Could you describe what you'd like `sp_RemoveViewsWithMaskingDependencies` to do? Are you trying to drop views that depend on column-level dynamic data masking in SQL Server, or something else?
Author: user
in SQL server 2019, can you help me create an extended event that captures any changes to jobs, job steps , or job schedules
Author: ChatGPT
Yes, we can set up an Extended Events (XE) session in SQL Server 2019 to capture changes made to SQL Server Agent jobs, job steps, and job schedules. This can be done by monitoring specific events related to the job management process, such as `object_altered` and `object_created`.
Here???s a step-by-step guide for setting up this Extended Event session:
### 1. Create the Extended Event Session
```sql
CREATE EVENT SESSION [CaptureJobChanges]
ON SERVER
-- Capture when jobs are created, altered, or dropped
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.username)
WHERE sqlserver.sql_text LIKE '%msdb.dbo.sp_add_job%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_update_job%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_delete_job%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_add_jobstep%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_update_jobstep%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_delete_jobstep%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_add_jobschedule%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_update_jobschedule%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_delete_jobschedule%'
)
-- Specify where to store the captured data
ADD TARGET package0.event_file
(
SET filename = 'C:\Temp\CaptureJobChanges.xel',
max_file_size = 10MB,
max_rollover_files = 5
)
WITH
(
STARTUP_STATE = ON
);
```
### 2. Explanation:
- **Events to Capture:**
- We are focusing on `sql_statement_completed` to capture any completed SQL statements related to job creation, modification, or deletion in SQL Server Agent.
- Filtering is done based on the stored procedures that are invoked when jobs, job steps, or job schedules are added, updated, or deleted in the `msdb` database:
- `sp_add_job`, `sp_update_job`, `sp_delete_job`
- `sp_add_jobstep`, `sp_update_jobstep`, `sp_delete_jobstep`
- `sp_add_jobschedule`, `sp_update_jobschedule`, `sp_delete_jobschedule`
- **Actions:**
- We're capturing `sql_text` and `username` to log the exact SQL command executed and the user who performed the action.
- **Target:**
- The target for the data is an event file stored at `C:\Temp\CaptureJobChanges.xel`. You can modify the path as needed.
- **Size and Rollover:**
- The session will store up to 10MB per file, and SQL Server will maintain 5 rollover files before overwriting older files.
### 3. Start the Extended Event Session:
Once the session is created, it will automatically start. If it???s not running, you can start it with the following command:
```sql
ALTER EVENT SESSION [CaptureJobChanges] ON SERVER STATE = START;
```
### 4. Query the Event Data:
You can retrieve the captured events with the following query:
```sql
SELECT
event_data.value('(event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS sql_text,
event_data.value('(event/action[@name="username"]/value)[1]', 'NVARCHAR(128)') AS username,
event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_time
FROM
sys.fn_xe_file_target_read_file('C:\Temp\CaptureJobChanges*.xel', NULL, NULL, NULL)
CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) AS XEvent;
```
### 5. Stop the Extended Event Session:
When you no longer need the session, you can stop and drop it:
```sql
ALTER EVENT SESSION [CaptureJobChanges] ON SERVER STATE = STOP;
DROP EVENT SESSION [CaptureJobChanges] ON SERVER;
```
This setup will capture changes to jobs, job steps, and job schedules in SQL Server Agent. Would you like to adjust anything further?
Author: user
can we add direct changes that might happen to msdb.dbo.sysjobs and msdb.dbo.syssteps
Author: ChatGPT
Yes, we can enhance the Extended Event session to capture direct changes to the `msdb.dbo.sysjobs` and `msdb.dbo.sysjobsteps` tables. Direct changes to these system tables (inserting, updating, or deleting records) can be tracked by monitoring Data Manipulation Language (DML) events such as `INSERT`, `UPDATE`, and `DELETE` on these tables.
Here???s how to modify the existing Extended Event session to also capture direct changes to these system tables:
### 1. Modify the Extended Event Session to Track DML Changes
```sql
ALTER EVENT SESSION [CaptureJobChanges]
ON SERVER
-- Capture DML changes on sysjobs and sysjobsteps tables (INSERT, UPDATE, DELETE)
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.username)
WHERE sqlserver.sql_text LIKE '%UPDATE msdb.dbo.sysjobs%'
OR sqlserver.sql_text LIKE '%INSERT INTO msdb.dbo.sysjobs%'
OR sqlserver.sql_text LIKE '%DELETE FROM msdb.dbo.sysjobs%'
OR sqlserver.sql_text LIKE '%UPDATE msdb.dbo.sysjobsteps%'
OR sqlserver.sql_text LIKE '%INSERT INTO msdb.dbo.sysjobsteps%'
OR sqlserver.sql_text LIKE '%DELETE FROM msdb.dbo.sysjobsteps%'
)
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS);
```
### 2. Explanation of Changes:
- **Added Monitoring for `sysjobs` and `sysjobsteps` DML Changes:**
- We're now looking for SQL statements that affect `sysjobs` and `sysjobsteps` directly, including:
- `UPDATE msdb.dbo.sysjobs`
- `INSERT INTO msdb.dbo.sysjobs`
- `DELETE FROM msdb.dbo.sysjobs`
- Similarly for `sysjobsteps`.
- **Other Adjustments:**
- You can further refine the session by capturing only the `INSERT`, `UPDATE`, or `DELETE` operations you are specifically interested in. This setup captures any SQL statements that directly modify the jobs or job steps by directly altering these system tables.
### 3. Querying the Event Data:
You can query the captured events, including those related to direct modifications of `sysjobs` and `sysjobsteps`, using the same query as before:
```sql
SELECT
event_data.value('(event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS sql_text,
event_data.value('(event/action[@name="username"]/value)[1]', 'NVARCHAR(128)') AS username,
event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_time
FROM
sys.fn_xe_file_target_read_file('C:\Temp\CaptureJobChanges*.xel', NULL, NULL, NULL)
CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) AS XEvent;
```
### 4. Starting and Stopping the Session:
Make sure the event session is running to capture all relevant events:
```sql
ALTER EVENT SESSION [CaptureJobChanges] ON SERVER STATE = START;
```
To stop it:
```sql
ALTER EVENT SESSION [CaptureJobChanges] ON SERVER STATE = STOP;
```
This enhanced setup now captures:
1. Changes to jobs, job steps, and job schedules via stored procedures (`sp_add_job`, `sp_update_job`, etc.).
2. Direct modifications (inserts, updates, and deletes) to the system tables `msdb.dbo.sysjobs` and `msdb.dbo.sysjobsteps`.
Would you like to make any additional adjustments?
Author: user
will the sqlserver.sql_text always contain the fully qualified name? what if the table was qualified with square brackets or double quotes? will the sqlserver.sql_text skip the event in that case?
Author: ChatGPT
Great question! The `sqlserver.sql_text` field captures the exact text of the SQL statement as it was executed, including whether the object names are fully qualified, enclosed in square brackets (`[]`), or double quotes (`""`).
### How `sqlserver.sql_text` Works:
- If a user qualifies the table name with or without a schema (e.g., `sysjobs` or `msdb.dbo.sysjobs`), or uses square brackets or double quotes, the `sqlserver.sql_text` will capture it as it appears in the executed SQL statement.
- **Square Brackets or Double Quotes**: These are common in SQL Server to handle reserved keywords or special characters in object names, and they would appear in the captured SQL text.
- **Fully Qualified vs. Partially Qualified**: If the schema is omitted and the user only references the table (e.g., `sysjobs` instead of `msdb.dbo.sysjobs`), that will also be captured exactly as typed.
### Issues with Relying on Exact Text Matching:
Since SQL statements can vary in how they reference tables, relying on `LIKE` filters for exact text matching in the `sqlserver.sql_text` might miss events. For instance:
- `UPDATE sysjobs` vs. `UPDATE msdb.dbo.sysjobs`
- `UPDATE [msdb].[dbo].[sysjobs]`
- `UPDATE "msdb"."dbo"."sysjobs"`
To capture all possible variations, you can use **more flexible matching logic**.
### Updated Solution with Flexible Matching:
We can modify the `WHERE` clause to handle these variations by using a less rigid matching pattern. Here's how you can make it more flexible:
#### 1. Adjust the `WHERE` Clause:
We can check for the presence of `sysjobs` or `sysjobsteps` without requiring specific qualification or delimiters.
```sql
WHERE sqlserver.sql_text LIKE '%sysjobs%'
OR sqlserver.sql_text LIKE '%sysjobsteps%'
```
This will capture any statement that mentions `sysjobs` or `sysjobsteps`, regardless of how it was qualified or enclosed.
#### 2. Alternative: Use Regular Expressions (if available):
SQL Server Extended Events do not directly support regular expressions, but if you use external tools to analyze the captured data (e.g., querying from logs), you could use regular expressions to match different variations of the table references.
### Final Adjusted Extended Event:
```sql
ALTER EVENT SESSION [CaptureJobChanges]
ON SERVER
-- Capture changes to sysjobs and sysjobsteps regardless of how they're qualified
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.username)
WHERE sqlserver.sql_text LIKE '%sysjobs%'
OR sqlserver.sql_text LIKE '%sysjobsteps%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_add_job%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_update_job%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_delete_job%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_add_jobstep%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_update_jobstep%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_delete_jobstep%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_add_jobschedule%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_update_jobschedule%'
OR sqlserver.sql_text LIKE '%msdb.dbo.sp_delete_jobschedule%'
)
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS);
```
This more general approach ensures that the event captures any modifications to `sysjobs` and `sysjobsteps`, regardless of the specific way the table was referenced in the SQL statement.
### Key Considerations:
- **Performance**: Using `%sysjobs%` will match anywhere in the statement, but could also include false positives. You might capture references to the word `sysjobs` even if it's part of a comment or different context. If this happens, you can refine the match.
- **Missed Matches**: If you want to ensure you never miss any changes, you could expand the criteria further to capture a wider range of table references or even check directly for `INSERT`, `UPDATE`, or `DELETE` operations on those specific tables through event targets like `sqlserver.sql_batch_completed` or `sqlserver.sql_statement_completed`.
Let me know if you???d like to optimize the solution further!
Author: user
i think the calls to the procedures woudl need the same treatment. for example, looking for '%sp_update_job%' instead of '%msdb.dbo.sp_update_job%'
Author: ChatGPT
Lowell prefers capturing SQL Server procedure calls in Extended Events by using general patterns like '%sp_update_job%' rather than fully qualified names like '%msdb.dbo.sp_update_job%'.
Author: tool
Model set context updated.
Author: ChatGPT
Yes, you're right! Just like with table names, SQL Server procedure calls could be referenced without the full schema qualification, so it's safer to search for a general pattern like `'%sp_update_job%'` instead of the fully qualified `'%msdb.dbo.sp_update_job%'`.
Here's the updated Extended Event session to handle both job-related stored procedure calls and direct DML changes to `sysjobs` and `sysjobsteps`, using more flexible matching:
### Updated Extended Event Session:
```sql
ALTER EVENT SESSION [CaptureJobChanges]
ON SERVER
-- Capture changes to sysjobs and sysjobsteps regardless of how they're qualified
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.username)
WHERE sqlserver.sql_text LIKE '%sysjobs%'
OR sqlserver.sql_text LIKE '%sysjobsteps%'
OR sqlserver.sql_text LIKE '%sp_add_job%'
OR sqlserver.sql_text LIKE '%sp_update_job%'
OR sqlserver.sql_text LIKE '%sp_delete_job%'
OR sqlserver.sql_text LIKE '%sp_add_jobstep%'
OR sqlserver.sql_text LIKE '%sp_update_jobstep%'
OR sqlserver.sql_text LIKE '%sp_delete_jobstep%'
OR sqlserver.sql_text LIKE '%sp_add_jobschedule%'
OR sqlserver.sql_text LIKE '%sp_update_jobschedule%'
OR sqlserver.sql_text LIKE '%sp_delete_jobschedule%'
)
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS);
```
### Key Adjustments:
- Now matching procedure calls like `sp_add_job`, `sp_update_job`, and similar without requiring the full qualification (`msdb.dbo`), making it more flexible and ensuring it captures all procedure calls.
This should capture any SQL statement that either directly alters the jobs and job steps tables or makes changes through relevant stored procedures, no matter how they are referenced.
Let me know if you'd like to further refine this!
Author: user
so i think we can make the action tests less chatty by simply doing this, right? WHERE sqlserver.sql_text LIKE '%sp_%_job%'
OR sqlserver.sql_text LIKE '%sp_%_jobstep%'
OR sqlserver.sql_text LIKE '%sp_%_jobschedule%'
OR sqlserver.sql_text LIKE '%sysjobs%'
OR sqlserver.sql_text LIKE '%sysjobsteps%'
Author: ChatGPT
Yes, you're absolutely right! Using a more concise pattern like `'%sp_%_job%'`, `'%sp_%_jobstep%'`, and `'%sp_%_jobschedule%'` will reduce the verbosity and ensure you're capturing any stored procedures that follow the typical naming conventions related to jobs, job steps, and job schedules.
This approach will also simplify the Extended Event session while still covering the important cases. Here's the updated session with your suggested filters:
### Optimized Extended Event Session:
```sql
ALTER EVENT SESSION [CaptureJobChanges]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.username)
WHERE sqlserver.sql_text LIKE '%sp_%_job%'
OR sqlserver.sql_text LIKE '%sp_%_jobstep%'
OR sqlserver.sql_text LIKE '%sp_%_jobschedule%'
OR sqlserver.sql_text LIKE '%sysjobs%'
OR sqlserver.sql_text LIKE '%sysjobsteps%'
)
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS);
```
### Key Benefits:
- **Simplicity**: The pattern-based matching ensures that any stored procedures or SQL statements involving jobs, job steps, and schedules are captured without listing each procedure or table name explicitly.
- **Reduced Noise**: This should make the session less "chatty" and more focused, reducing the number of irrelevant events that might otherwise be captured.
This should work well for capturing the relevant changes while keeping the event session more efficient.
Let me know if you'd like to test it or make further adjustments!