Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace which is not the most intuitive tools. However in the latest editions, we are now given quite a lot of power for auditing with some purpose built tools. The SQL 2008 Audit is meant to be replacing SQL Trace, it is faster, more granular and easier to interact with from SQL Server Management Studio, and with code. In this article, I am using a T-SQL code approach.
Since this is one of the big topics in SQL Server 2008 (and bound to become more popular), it is not possible to cover it completely here. However, I will try to give you one detailed but simple example as an introduction to SQL Audit.
I will store log in D:\Audit in my server, you can change the path in given script if you wish, do create the folder and give reference in script below.
Yesterday I was reviewing one of my latest projects and I found that one of the developers had created two stored procedure with “SP_” prefix. Generally “SP_” prefix is used for System stored procedure name; look at one of my past article how to create your own system SP, if You will create your own stored procedure with “SP_”, It will not give you any error, it will get executed successfully but it is really a overhead on processing as whenever compiler will get prefix “SP_” it will going to look into system SPs list, once it won’t get it from there, will look at user SP list. Apart from this, there should be some naming convention rules for user defined object that is what I believe so I thought to put some policy enforcement so that, even by mistake, nobody can do it.
Finally you are giving Policy Name “Stored Procedure Naming Convention Policy”, making it enable, which is MUST, by check box given. Select the condition you created in step # 2 and also selecting the target from given list. Once you done with that, don’t forget to select “On Change: Prevent” Evaluation Mode and also don’t forget to click on “OK” button
Error “performance counter registry hive consistency” while installing SQL Server 2008 is really frustrating error. I have installed Microsoft SQL Server 2008 Evaluation Edition quite a few times but this is first time I got error “performance counter registry hive consistency”. This error itself shows that there is a problem in registry of performance counter so the prime question is, how to resolve this issue? There may be few different ways but I use “LodCTR.exe” file.