Solved

Question on Audit group - DATABASE_OBJECT_CHANGE_GROUP

Posted on 2016-11-04
6
24 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 17

Expert Comment

by:Pawan Kumar Khowal
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 11

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now