Server audits - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Server audits

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-12-08 : 13:57:12

Hello All,

I need to capture these audits in SQL table. Can anyone give me the direction/suggestion on best way to achieve this task?

Event only by sysadmin users (SQL login or domain login)
-Failed login
-Admin account activity
-Failed resource access attempts
-password change
-system configration changes
-Deletion of event logs
-system level objects - creates and alters

Can anyone help me in this?

Thanks,

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-09 : 16:53:42

Here are some ideas:

-Failed login: This is in the SQL log if you configure for it at the server level (Server Properties -> Security -> Login auditiing -> Failed logins)
-Admin account activity" Not sure what you mean by this (???)
-Failed resource access attempts: Configure C2 Auditing at the Server level
-password change: Define a Server Level Trigger for this event and either notify someone (emails, et al) or log the details.
-system configration changes: ???
-Deletion of event logs: ???
-system level objects - creates and alters: At the database level (each database), define a DDL trigger which responds to the events you care about and, again, notify, log or deny the action.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

Kristen
Test

22859 Posts

Posted - 2011-12-10 : 07:24:22

"-system level objects - creates and alters: At the database level (each database), define a DDL trigger which responds to the events you care about and, again, notify, log or deny the action."

Bustaz Kool gave me some code for that

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168032#657441

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-12 : 13:07:43

Here is some code that creates a server level trigger to notify me whenever a Login gets changed. We have similar ones for Login creation, User creation and Role membership.[CODE]CREATE TRIGGER [login_changes]
ON ALL SERVER
FOR ALTER_LOGIN
AS
BEGIN
declare
@data XML = EventData(),
@subject varchar(100),
@body varchar(max);

set @subject = @@SERVERNAME + ' - ' + SUSER_NAME() + ' - Check Logins!'

SET @body =
cast((select SUSER_NAME()) as varchar(max)) + ' Server Name : ' + ( @@SERVERNAME)+ ' changed a login.' + char(13) + char(10) +
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') + char(13) + char(10) +
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)');

EXEC dbo.send_email -- This is a sproc that performs the email operation (Well, duh!)
'My Email Profile',
dba@MyDomain.com',
@subject,
@body
END[/CODE]

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources