How to capture SQL login details with Extended Events

An application owner wanted to record the usage of a specific logon and assess where the connections were coming from Read more →

How to capture deadlocks using system_health Extended Events

Find SQL Server deadlock details easily with Extended Events Read more →

How to find database objects created with SQL default trace

How can you find information about SQL database objects created? We're talking about CREATE DATABASE,CREATE INDEX,CREATE PROCEDURE,CREATE DATABASE. There are common scenarios where you may require these details. For example: 1) It is the organisation's sql server security policy for any production database to progress through a change request and review process. Monitoring database objects added , can identify developers attempting to avoid the review process. 2) Attempting to identify rogue processes. For example - software added to manage the server introduces new database processes - such as database tables 3) Troubleshooting an outage. An unexpected service impact occurs and... Read more →

How to get user security changes with SQL default trace

Question: There were some recent user security changes on a SQL Server login user account. Is there a way to grab this information - via the default trace? I haven't got a dedicated Extended events monitor set up - so I'm relying on the default trace . Management want to understand the details related to this security change. Answer: It is possible to report on security changes administered on SQL Server via the default trace. In fact - it's excellent - except if the default trace configuration is set up to rollover the files. Recently I had to use the... Read more →

How to Audit user DML with SQL Server Extended Events

Question: What methods are available to audit the DML activity of a user. I need to track a third party developer and record the DML statements they execute on a SQL Server data? The data needs to be recorded and made available for a regular review. Answer: There are many ways to skin the audit cat. Since SQL Server 2008 the DBA life has become easier with the introduction of Extended events , as applied to auditing. Before I explain of how to set up Extended Events let’s review some other methods Manual auditing – refers to a home grown... Read more →