{"id":513,"date":"2011-12-01T21:43:00","date_gmt":"2011-12-01T21:43:00","guid":{"rendered":"\/blogs\/bobb\/post\/Auditing-database-level-objects-in-SQL-Express-2012.aspx"},"modified":"2013-01-03T23:59:28","modified_gmt":"2013-01-04T07:59:28","slug":"auditing-database-level-objects-in-sql-express-2012","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/","title":{"rendered":"Auditing database-level objects in SQL Express 2012"},"content":{"rendered":"

\n… Continued from previous blog entry<\/a> … \n<\/p>\n

\nThe point of using a Server Audit Specification with database events in SQL Server 2012 is this. In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition. In SQL Server 2012, one of the new features (to quote BOL) is: "Support for server auditing is expanded to include all editions of SQL Server. Database audits are limited to Enterprise, Datacenter, Developer, and Evaluation editions." (Note: there's no DataCenter edition any more after the recent licensing change).\n<\/p>\n

\nSo there IS a SQL Express 2012 RC0 available. Let's download it and try it out. [Starts download. Waits 5 minutes. Starts install. Done in 5-10 minutes. Nice].\n<\/p>\n

\nThere is no GUI for Server Audit and Server Audit Specification, even when you load up an Express edition into a Enterprise SSMS Object Explorer. But who needs a GUI…we can use DDL and use sys.fn_get_audit_file to read the audit log.\n<\/p>\n

\nUSE master
\nGO
\nCREATE SERVER AUDIT TestAudit2 TO file (filepath = 'c:\\temp')
\nWHERE database_name ='AdventureWorks' AND schema_name ='HumanResources'
\n  AND object_name ='EmployeePayHistory' AND database_principal_name ='dbo';\n<\/p>\n

\nCREATE SERVER AUDIT SPECIFICATION TestServerSpec2
\nFOR SERVER AUDIT TestAudit2
\n    — ADD SELECT ON HumanResources.EmployeePayHistory BY dbo) — note: you can't do this in SERVER AUDIT SPEC
\n    ADD (SCHEMA_OBJECT_ACCESS_GROUP)
\n    WITH (STATE = ON);
\nGO\n<\/p>\n

\nAs my friends in the UK would say: "Works a treat!". And Books Online is correct, attempting to define a Database Audit\n<\/p>\n

\nSpecification in SQL Express returns:\n<\/p>\n

\nMsg 33075, Level 16, State 3, Line 1
\nGranular auditing is not available in this edition of SQL Server. For more information about feature support in the editions of SQL Server, see SQL Server Books Online.\n<\/p>\n

\nSo you can't do without Database Audit Specs is audit Database Level audit actions. Like INSERT, SELECT, or DELETE. So maybe you're not *supposed to* be able to specify action_id in a Server Audit Specifcation filter predicate. Although you can't specify these (action_ids in predicate filters) for Server-level objects either. Even in Enterprise edition.\n<\/p>\n

\nStill, having seen auditing with SQL Express and only SERVER AUDIT filters, it's a MUCH more powerful and compelling feature than I'd imaged when I first heard of it. Especially after seeing folks try to shoehorn auditing using CDC. Or change tracking. Or use triggers on every action. And BTW, you are limited to 3000 characters in a filter predicate, although they seem to allow AND\/OR\/NOT and the standard comparison operators. No "LIKE" capability, but you don't get that it SQL Server auditing anyhow. \n<\/p>\n

\nIn any case… enjoy auditing in SQL Express! And Standard Edition. And BI Edition. And no, I haven't tried it on LocalDB yet.\n<\/p>\n

\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"

… Continued from previous blog entry …  The point of using a Server Audit Specification with database events in SQL Server 2012 is this. In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition. In SQL Server 2012, one of the new features (to quote BOL) is: "Support […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,31],"tags":[],"class_list":["post-513","post","type-post","status-publish","format-standard","hentry","category-security","category-sql-server-2012"],"yoast_head":"\nAuditing database-level objects in SQL Express 2012 - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Auditing database-level objects in SQL Express 2012 - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"… Continued from previous blog entry …  The point of using a Server Audit Specification with database events in SQL Server 2012 is this. In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition. In SQL Server 2012, one of the new features (to quote BOL) is: "Support […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2011-12-01T21:43:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:28+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/\",\"name\":\"Auditing database-level objects in SQL Express 2012 - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2011-12-01T21:43:00+00:00\",\"dateModified\":\"2013-01-04T07:59:28+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Security\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/security\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Auditing database-level objects in SQL Express 2012\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Auditing database-level objects in SQL Express 2012 - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/","og_locale":"en_US","og_type":"article","og_title":"Auditing database-level objects in SQL Express 2012 - Bob Beauchemin","og_description":"… Continued from previous blog entry …  The point of using a Server Audit Specification with database events in SQL Server 2012 is this. In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition. In SQL Server 2012, one of the new features (to quote BOL) is: "Support […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/","og_site_name":"Bob Beauchemin","article_published_time":"2011-12-01T21:43:00+00:00","article_modified_time":"2013-01-04T07:59:28+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/","name":"Auditing database-level objects in SQL Express 2012 - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2011-12-01T21:43:00+00:00","dateModified":"2013-01-04T07:59:28+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/auditing-database-level-objects-in-sql-express-2012\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Security","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/security\/"},{"@type":"ListItem","position":3,"name":"Auditing database-level objects in SQL Express 2012"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/513","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=513"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/513\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}