• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1131
  • Last Modified:

SQL Server 2008 DML and DDL commands Auditing

I have enabled Databse Auditing for DML commnds. But I am not able to select multiple object names and multiple Principal name in a single action row. We have 40 tables and 20 Principal Names.

Please let me know how can I audit DML and DDL commands using this inbuilt auditing feature in SQL server 2008 or using script?

Thanks in advance....
 
0
didba
Asked:
didba
  • 4
  • 3
4 Solutions
 
Eugene ZCommented:
looks like you need to create it as object by object
 
like
http://technet.microsoft.com/en-us/library/cc280404.aspx 
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payrole_Security_Audit
ADD (SELECT , INSERT
     ON HumanResources.EmployeePayHistory BY dbo )
WITH (STATE = ON)
 
0
 
didbaAuthor Commented:
I have already read http://technet.microsoft.com/en-us/library/cc280404.aspx 

But I have 35 tables and 22 Principal names . SO I need to write atleast 770 lins like ADD (SELECT , INSERT ON HumanResources.EmployeePayHistory BY dbo ).

that is the problem...is there any shortest way to do it?
0
 
Eugene ZCommented:
it is not good:
you need 770 audits and 770 db audit specs - looks like you need not by object but like Shema or database
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
didbaAuthor Commented:
I did manually and created 770 audit spec. there is no other solution. May be in the next version of SQL server, it will be easy.
0
 
Eugene ZCommented:
wow -  good job!!
 you may try to do it by schema  instead of by object: it  can  be your better solution
maybe you will feel better (check it time -to-time)
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=416357
0
 
Eugene ZCommented:
as fact : the solution was used "you need 770 audits and 770 db audit specs "
"I did manually and created 770 audit spec"
however, I  do not care if the question will be closed - if didba think it needs to be closed
0
 
didbaAuthor Commented:
I can't go with Schema. that schema has 128 tables. I have posted the question here to get the better solution but I couldn't find it. That's why I decided to close it. I appreciate your suggestions and comments.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now