Solved

Question on Audit group - DATABASE_OBJECT_CHANGE_GROUP

Posted on 2016-11-04
6
50 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

740 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