Solved

Question on Audit group - DATABASE_OBJECT_CHANGE_GROUP

Posted on 2016-11-04
6
46 Views
Last Modified: 2016-11-20
Hello everyone,

   I tried configuring DDL audit for my production databases, while doing so I am unable to find ways to selectively collect data for few logins and leaving the other logins used by applications.

But in case of DML operations such as select,update,delete,execute are able to be configured for each user individually.

Could someone shed some light on why aren't we able to configure the DDL statement audit for each individual logins?

Environment details: SQL server 2008 R2 service pack 2
thanks
Deepak
0
Comment
Question by:Deepak Kumar
  • 3
  • 2
6 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41873691
Are you getting any error while doing this ?

Try this

EXEC sp_readerrorlog 0, 1, 'Login failed'  

EXEC xp_readerrorlog 3;

<< Note - I think it is undocumented >>
0
 

Author Comment

by:Deepak Kumar
ID: 41873945
Hi Pawan,

Thanks for your reply. Here I am talking about the SQL server Audits which we configure under Security->Audits folder in the Object Explorer.

thanks
Deepak
0
 
LVL 13

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 500 total points
ID: 41874647
When creating the database audit definition, you can provide principals to filter the audit on: https://msdn.microsoft.com/en-us/library/cc280404.aspx

Can you please try it out? I don't have a SQL instance with me right now to confirm.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41875063
Hi Deepak,

I think you should follow this URL - https://mssqltalks.wordpress.com/2013/02/25/how-to-audit-login-to-my-sql-server-both-failed-and-successful/

Its really good, everything is given step wise with screenshots.

Regards,
Pawan
0
 

Accepted Solution

by:
Deepak Kumar earned 0 total points
ID: 41887763
Hi Folks, thanks for your reply. I would like to close this question as i found the solution and partly Nakul's comment had helped. The question which i asked might have confused but the answer for the question is mentioned below:

My main requirement was to setup an Audit just like other audits like INSERT, SELECT, etc., for few users(here i am referring windows authenticated users). I was able to setup the audits and manage to configure for users who l like to audit but however our client had asked why can't we able to make the same type of audit setup for DDL statements for users who we like to rather than configuring for entire database or audit setup at server level for all databases?

The answer basically is, We don't have any option provided in audit setup . We cannot able to configure users specific audits for any audits which has the audit group name ends with _group

eg.,DATABASE_OBJECT_CHANGE_GROUP.

When we configure this audit group, the audit will start capturing the audit records of DDL statements of all the users who fires the DDL statements in the database.

thanks
Deepak
0
 

Author Closing Comment

by:Deepak Kumar
ID: 41894660
I have tried my solution and tested it
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question