Snippet Name: Enhance_dSQL_Server_Events

Tags:

Created Date: 2025-06-16 13:12:41
Last Modified Date: 2025-05-31 10:32:05

Dependencies:

DBA Notes:

Sample Output:

Title: Enhance dSQL Server Events
Author: user
can you help me enhance the attrache dSLQ Server Extended Event, which currnet captures DDL changes. I need to enhance it to capture any sql text that features '%ALTER%SERVER%' , '%ALTER DATABASE%' or '%sp_configure%

Author: ChatGPT
Thanks for uploading your Extended Events script. I'll review the contents of your `MyEE_DDL.sql` and then show you how to enhance it to also capture SQL text matching:

- `%ALTER SERVER%`
- `%ALTER DATABASE%`
- `%sp_configure%`

Let me take a look at the file first.

Author: ChatGPT
Your current Extended Events session (`EE_DDLChanges`) captures the following events:

- `database_created`
- `object_created`
- `object_altered`
- `object_deleted`

Each event includes `sql_text` and filters out tempdb-related chatter, uncommitted DDL phases, and some object types.

---

### ???? Goal: Enhance to capture commands like:
- `ALTER SERVER`
- `ALTER DATABASE`
- `sp_configure`

These may not be caught by the existing DDL-focused events because they are configuration-level and not tied directly to object-level DDL.

---

### ??? Recommended Enhancements

Add the following to your session:

#### 1. **Capture `sql_batch_completed`** ??? catches regular SQL text execution:
```sql
ADD EVENT sqlserver.sql_batch_completed
(
    ACTION (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.nt_username,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE (
        sqlserver.sql_text LIKE '%ALTER SERVER%' OR
        sqlserver.sql_text LIKE '%ALTER DATABASE%' OR
        sqlserver.sql_text LIKE '%sp_configure%'
    )
),
```

#### 2. **Capture `rpc_completed`** ??? for `EXEC sp_configure` and similar:
```sql
ADD EVENT sqlserver.rpc_completed
(
    ACTION (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.nt_username,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE (
        sqlserver.sql_text LIKE '%ALTER SERVER%' OR
        sqlserver.sql_text LIKE '%ALTER DATABASE%' OR
        sqlserver.sql_text LIKE '%sp_configure%'
    )
),
```

---

### ???? Where to Add These
Insert them in your `CREATE EVENT SESSION` statement alongside the existing `ADD EVENT` blocks.

---

Would you like me to generate the full enhanced script for you including the above additions?

Author: user
no, that was exactly what iw as after

Author: ChatGPT
Perfect! Glad that hit the mark. If you end up needing filters refined further (e.g., capturing only certain users or excluding management tools), just let me know.

Happy auditing, Lowell!