Auditing

Timothy Smith

Monitoring changes in SQL Server using change data capture

May 27, 2016 by

Background

In multi-user environments, changes may occur frequently to the architecture, data, or overall structure that creates work for other users. In this series, we look at some ways that we can track changes on the data and architecture layer for pin-pointing times, changes, and using the information for alerting, if changes should be kept to a minimum. SQL Server comes with some built-in tools that allow us to monitor changes, and depending on the architecture, we can create tools that allow us also to monitor and identify changes near the time that they occur.

Read more »
Ahmad Yaseen

How to track changes in SQL Server

March 8, 2016 by

As a part of a Big Data project, we are often asked to find the best way to track the changes applied to the database tables, so that, no requirement is created to load all the huge tables to the data warehouse database at the end of the day, if not all of the data was changed.

Read more »
Kimberly Killian

Simple SQL Server automated user auditing

January 25, 2016 by

As a DBA I am often asked to provide lists of all active users ID’s or groups for a specific server or database. In addition to this, I am also asked to provide a list of failed logins. These requests are frequent enough that I decided to automate the process of gathering this data and provide two nicely formatted HTML emails. I am not going lie, manager types love these reports, and anything that makes managers love my work I am all about! I’m also all about automating anything that makes my job easier. Call me lazy or call me prepared, I hate having to do something over and over that I could easily throw into an SSIS package or Agent Job and just schedule it to do it for me. This entire process consists of using SQL Server Integration Services (SSIS), 4 tables and a SQL Agent Job containing 2 reports (Failed Logins and Active SQL Server Users). The SSIS package, along with all of the queries and scripts are attached at the end of the article.

Read more »
Kenneth Fisher

Reviewing the SQL Server Audit

December 25, 2014 by

Deciding what method to use to collect audit information can be a task in and of itself. Depending on your needs there are a number of different ways to collect the information. There are settings such as Login Auditing and Common Criteria Compliance that are fairly simple to implement but only collect very specific sets of information. There are also coding solutions such as Triggers and Audit Columns that are very flexible but can be tricky to get right. Then there are Profiler Traces but those have been deprecated and will be removed in a future version of SQL Server. Their replacement, Extended Events, has become easier and easier to use in recent versions and can be used to collect a huge variety of information. However Extended Events does not include any Audit specific information. If you are setting up an auditing solution in SQL Server you are probably going to want to consider using SQL Server Audits. Audits are based on Extended Events and so have all of the benefits of Extended Events but also include the audit specific events. Audits have a different interface than Extended Events and a different set of T-SQL Commands. Fortunately they are very easy. Read more »

Kenneth Fisher

Intro to Auditing in SQL Server

September 30, 2014 by

In the world of information, auditing serves an important purpose. It helps to provide an assurance that the data involved is accurate and safe. The level of assurance of course depends on any number of factors including the level of trust in those performing an audit (or collecting the data for the audit), the frequency the data is collected, and the types of data collected.

Any time the collection of data is this important to a process you can bet that a DBA is going to be involved. We get asked to create and run queries to pull data (frequently ridiculously complex queries, and rarely some simple ones). You could almost make it into a joke. Read more »