Solved

Question on Audit group - DATABASE_OBJECT_CHANGE_GROUP

Posted on 2016-11-04
6
55 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 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