SQL Server Audit

SQL Server Audit Walkthrough

So, you are the Database Administrator in your company and your boss wants you to “have a look” at a SQL Server that you would be managing from now on.  You didn’t know about it before, somebody else had installed it before you came on board, but now your work is to make sure it’s […]

Auditing with Microsoft Assessment and Planning (MAP) Toolkit 5.0 – Part 1

Auditing and inventorying is not something new in any IT environment. Depending on the purpose, it can take many forms. Sometimes the auditing is about security and data access. Sometimes it is about software usage and licensing. Often it is about consolidation and cost saving. Whatever the reason, the strategic direction typically comes from top […]

Automate Audit Requests

User Review Periodically, an internal auditor would stop by my desk and ask me to show them all the users and permissions on a particular database.  This is easily gathered, and most of us know how to do this.  I produce this list, and send it on to the requestor.  The next question is ‘What […]

Implementing Transactions in SQL Server – Part II

In Implementing Transactions Part I I briefly described the role of Transactions in SQL Server and outlined a very basic implementation. In this second part, I will explain how a DBA can best implement Transactions in scripts that are to be deployed on production databases. One of the regular tasks of a DBA is to […]

Auditing with Microsoft Assessment and Planning (MAP) Toolkit 5.0 – Part 3

Software Usage Tracker New to MAP 5.0 is another feature called the Software Usage Tracker. This is an audit mechanism that allows you to track the number of users or devices accessing your servers. The information from these audits can help people responsible for managing software licensing to get a better picture of Microsoft server […]

Auditing with Microsoft Assessment and Planning (MAP) Toolkit 5.0 – Part 2

Using MAP to inventory your servers When you start MAP, the application prompts you for a database name (see figure below). At the very beginning there will be no database to hold MAP inventory information, so you will need to specify a new database name. You can name it anything, but for our purpose, I […]

Retrieving Data from an Audit Table

Retrieving data from an audit table. Retrieving information from a history table for a given point in time is a common data task. For example, using a table that contains all historical prices, what where the prices of all products as they were 30 days ago? There are a number of ways to different methods […]

Auditing in SQL Server 2008

Introduction Auditing is the monitoring and recording all user actions on a database. You can base auditing on individual actions, such as database backup, change of user logins, insert etc. or on combination of factors. Why Audit? Though auditing has become a popular buzzword, many DBAs and developers still do not appreciate the requirement for […]

SQL Server Security Audit (Part 3) – Operating System Level Audit

Operating system level audits Typically, most DBAs have remote access privilege to the Windows machine hosting the database server. If you have administrator privilege in the Windows box (or VM), you can take some time to try the following: Windows security log This should be actually a part of the DBA’s daily checks. However, as […]

SQL Server Security Audit (Part 1) – Server Level Audit

Although security is a major component of database administration, it is sometimes overlooked in favour of convenience. User accounts are given elevated permissions to save time, patches and hot-fixes are not applied timely and best practices are often not followed. Over time, the server becomes vulnerable to potential breaches of security. As the DBA, you […]