Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Question on Audit group - DATABASE_OBJECT_CHANGE_GROUP

Posted on 2016-11-04
6
Medium Priority
?
99 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 35

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 14

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 35

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

877 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