Using the Service Audit Object in SQL Server 2008

Question

Using the Service Audit Object in SQL Server 2008

K. Brian Kelley

SSC Guru

Points: 114642
More actions
December 18, 2008 at 1:23 am

#94616

Comments posted to this topic are about the item Using the Service Audit Object in SQL Server 2008
K. Brian Kelley
@kbriankelley

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

anand.ramanan SSChasing Mays Points: 647 More actions · Answer 1

You mention that SQL Server Audit object is available in any version of SQL other than Express, however, Audit is only available in 2008 Enterprise. Is there a water down version of auditing available in versions other than 2008. What alternatives are available to perform similar auditing when not using Enterprise edition?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

What I said in the article was that just about every edition above Express *could* use it. But Microsoft chose to only put it in Enterprise Edition. For those of us who started using Standard Edition for 2-node HA clusters, this hurts. Standard Edition is economical and meets our needs. Enterprise Edition is pricey. But if we want to use the Audit object, we've got no choice but to use Enterprise.

As far as alternatives, there's nothing similar to it in the other versions.

K. Brian Kelley
@kbriankelley

Anipaul SSC-Insane Points: 24681 More actions · Answer 3

Anipaul

SSC-Insane

Points: 24681

December 21, 2008 at 1:49 am

#915808

Great article...

andynold SSC Journeyman Points: 83 More actions · Answer 4

What are recommended options for auditing tools and/or utilities for people that are using the MS-SQL standard and web editions?

We have a public facing web site where our biggest auditing need (in addition to what we can see in the regular OS event logs) is to track DBA actions.

Atiq Rahman-266579 SSC Veteran Points: 286 More actions · Answer 5

Hi Brian,

Very nice article. I was able to configure the auditing but I am not getting the parameter values in the audit.

Ex. If some executed a query as follows:

select name, age from dbo.employees where empno = 100.

This above query is being audited as follows:

select name, age from dbo.employees where empno = @1.

How can I get the value of @1?

Regards,

Atiq Rahman

Atiq Rahman-266579 SSC Veteran Points: 286 More actions · Answer 6

Hi Brian,

Very nice article. I was able to configure the auditing but I am not getting the parameter values in the audit.

Ex. If some executed a query as follows:

select name, age from dbo.employees where empno = 100.

This above query is being audited as follows:

select name, age from dbo.employees where empno = @1.

How can I get the value of @1?

Regards,

Atiq Rahman

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

This may be one of the weaknesses in the Audit Object. I need to play around a bit more to see if you can capture the parameter, but at first glance, I'm not sure you can.

K. Brian Kelley
@kbriankelley

abbala2008 SSC Enthusiast Points: 100 More actions · Answer 8

Hi,

Nice article....You have done good job....Thank you.....

---------------------------------------

Auditing Tools

Wayne West SSC-Insane Points: 22586 More actions · Answer 9

Brian, thanks much for this article! It was exactly what I needed to get a test system going to explore what the audit offered. I had been fiddling with it to no avail, read your article, and had it running shortly thereafter.

I was a little disappointed that the file export was to log format, I was hoping I could get it out as a CSV. There is the export function, I think I'll be looking in to automating dumping the contents to a text file.

I think it's basically a good thing, but it still has some growth.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 10

Great article.

Is there a way to roll over the logs on a time basis or manually?

I'm thinking of some sort of

DBCC ERRORLOG

sp_cycle_errorlog

equivilant

Achilies SSCommitted Points: 1695 More actions · Answer 11

Good article,

I know to view audit logs, we can right click from SSMS to the audit specification and view them if the object has made the destination to a file. But how to view the audit logs, if the destination has given to windows/application log. Right now i am viewing the audit logs (given to windows/application log) going to event viewer and opening the windows logs. Is there any way that we view these right from SSMS?

Thanks..

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 12

Beginner_2008 (3/5/2012)
Good article,
I know to view audit logs, we can right click from SSMS to the audit specification and view them if the object has made the destination to a file. But how to view the audit logs, if the destination has given to windows/application log. Right now i am viewing the audit logs (given to windows/application log) going to event viewer and opening the windows logs. Is there any way that we view these right from SSMS?
Thanks..

I do not have my audit files set up this way, so can not be sure, but have you looked under Management, SQL ServerLogs. here you can select logs for Windows NT, application