Auditing SELECT statements in SQL Server 2008

Prior to SQL Server 2008, the only way to audit the SELECT statements is to use SQL Server Profiler or server side trace. Now using SQL Server 2008 Enterprise, auditing feature is used to audit on SELECT statements. We cannot use triggers as triggers are not fired on SELECT statements.

Audit object is used to monitor various sever and database level events without the need of full trace.

We need to create an Audit object and the Database Level Audit Specification in order to monitor when a SELECT statement is issued against a particular table.

Creating an Audit

  1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.
  2. Expand Security Node and select Audits.
  3. Right click Audit and click ‘New Audit’ to launch the new Audit dialog.
  4. We need to enter Audit name and the audit destination.

Audit destination can be of Application Log event, Security Log Event, File (or folder). In case of file, a path needs to be entered and the directory should exist. Also need to configure Maximum rollover and maximum file size properties.

  1. Click Ok to create the audit.

 

T-SQL:

CREATE SERVER AUDIT [Audit_Select_Production_Product]
  TO FILE ( FILEPATH = N'c:\temp\selectAudit'
  ,MAXSIZE = 0 MB
  ,MAX_ROLLOVER_FILES = 2147483647
  ,RESERVE_DISK_SPACE = OFF)
WITH
(QUEUE_DELAY = 1000,ON_FAILURE = CONTINUE)

Creating Database Level Audit Specification:

  1. Select the database where the db level audit specification needs to be created.
  2. Expand Security under the particular database node and select ‘Database Audit Specifications’
  3. Right Click the Database Audit Specifications and select ‘Create new Database Audit Specification’.
  1. We need to enter audit name and need to select the server level audit which we created above.Also need to select Audit Action type, object and principal. The audits are logged only when the particular principal name executes a SELECT statement. In case if the audit needs to be logged for every one issuing a SELECT statement, then the principal name should be ‘public’.
  2. Click ok to create a database level audit specification.

 

T-SQL

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20090105-115555]
  FOR SERVER AUDIT [Audit_Select_Production_Product]
  ADD (SELECT ON OBJECT::[Production].[Product] BY [Peter])

Viewing Audit Logs:

After executing SELECT statements, in order to view the audit logs, follow the below steps

>Expand Security->Audits-> and select the audit created above.
  • Right click and click ‘View Audit Logs’ from the context menu to launch the audit log viewer dialog.
  • It contains the information about the SELECT statements issued on the particular object.
  • via: http://blogs.msdn.com/b/sreekarm/archive/2009/01/05/auditing-select-statements-in-sql-server-2008.aspx

    Unable to modify table, timeout expired errors with Microsoft SQL Server

    While modifying a table in Microsoft SQL Server with about 10 million records in it, I got the error message “unable to modify table – timeout expired…” and the table was not able to be modified. The solution was to change the timeout settings which is covered in this post.

     

    When I first attempted to save my changes to the table, I got an initial warning after a few seconds as shown in the screenshot below.
    warnings were encountered during the pre-save validation process

    The full text from the message in the above screenshot is “Warnings were encountered during the pre-save validation process, and might result in a failure during save. Do you want to continue attempting to save?” And then “Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.” Naturally I clicked the “Yes” button after getting this warning because I really did want my changes saved. Also I’d paused the process which updates this table so it wouldn’t result in any errors while I was saving the new table structure.

    After 30 seconds, I got the error message in the following screenshot.

    errors were encountered during the save process

    The full text from the above screenshot is “Errors were encountered during the save process. Some database objects were not saved” and “Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.” The changes to the table that I had attempted to make were not saved and the table structure was the same as it had been previously.

    The issue is caused by a timeout setting for “table designer updates” which is set to 30 seconds as the default value. If your changes cannot be saved in this time then they will not be. To change the setting, go to “Tools” and then “Options” from the main menu of your SQL Server Management tool. Then click on the “Designers” section of the options. This is shown in the screenshot below.

    changing the transaction timeout

    The value that needs to be changed is highlighted with a red box in the screenshot above and is labelled “Override connection string time-out value for table designer updates” and “Transaction time-out after”. Change the default 30 seconds value to some higher value, click the “OK” button and run your table structure change again.

    Note that the transaction time-out setting must be a numerical value greater than 0. Setting it to 0 to prevent any timeouts will not work and you will be presented with an error message as shown in the screenshot below.

    valid range is 1 to 65535

    You can set it to any value between 1 and 65535. Given that this is in seconds, 65535 is over 18 hours so setting it to the maximum value would normally work. For my table with 10 million records, I set it to 600 seconds (10 minutes) to see if that would work. The actual table save took more than 10 minutes but it didn’t time out, which surprised me, and the new table structure took affect after that.

    Note that while the table structure is being saved, the SQL Server Management tool is completely inaccessible and the window is usually blanked out like it has crashed. Just wait until it’s all done and you should be alright.

    via: http://www.electrictoolbox.com/unable-modify-table-timeout-expired-sql-server/

    MariaDB adds NoSQL features to relational database roots

    SUMMARY:MariaDB 10 is out, featuring a “Connect engine” that makes it easier to handle data from both traditional SQL databases and more web-scale NoSQL systems. The new functionality merits new editions of the MariaDB Enterprise and Enterprise Cluster products.

    SkySQL has released new versions of MariaDB Enterprise and MariaDB Enterprise Cluster, promising that these editions will combine the consistency of traditional SQL database technology with the scalability of NoSQL.

    It’s now almost a year since SkySQL merged with Monty Program, bringing back together a lot of the old MySQL team — around 50 of them, including Monty Widenius and David Axmark — to take on the current MySQL proprietor, Oracle, with the MariaDB fork of the venerable database system.

    MariaDB was always pitched as a “bridge” between MySQL and NoSQL databases like Cassandra and LevelDB. Now the MariaDB Foundation has released version 10of the open-source database, bringing in several NoSQL-esque capabilities that make MariaDB Enterprise 2 a more enticing package.

    The big addition is the Connect engine, which provides quick access to unstructured files, for example log files in a folder, from within MariaDB. Cassandra data can also be accessed from within MariaDB 10, and “dynamic columns” also allow NoSQL-style storage of variously-labelled objects in each row. Apart from that, MariaDB 10 is also supposedly much faster and more stable than previous versions.

    SkySQL sales chief Dion Cornett suggested to me that enterprise users would find value in being able to glue together the various file and data formats they might encounter.

    “What if your online store sells and fulfills orders from a broad range of different suppliers, and your systems must access and combine their inventory records — stored in different SQL databases from Oracle to MySQL to PostgreSQL to SQL Server?” Cornett posited. “Combining such different data sources together in real time has required a lot of hand-coded logic — a costly and time-consuming proposition.”

    As for MariaDB Enterprise Cluster 2, a new user console and management API aims to simplify the deployment of Galera clusters, a movement that should appeal to those with high-availability needs.

    via: http://gigaom.com/2014/03/31/mariadb-adds-nosql-features-to-relational-database-roots/