We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SQL Server 2008 DML and DDL commands Auditing

didba
didba asked
on
Medium Priority
1,174 Views
Last Modified: 2012-05-06
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....
 
Comment
Watch Question

EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
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)
 

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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?
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
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
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.
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
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
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
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

Author

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.