turn on DDL trigger for SQL server 2008 R2 stnadared

Dear all,

right now try to receive email/trigger alert with information on:

1) what login has been created and who create it !
2) What login has been deleted and who delete it!
3) what SQL job has been deleteed and who delete it!
4) what SQL job has been created and who create it!
5) what has been changed on SQL server configuration and who change it?

I head that SQL server 2008 R2 has simple built in tools for that and please share how you guys implement your solution, step by steps please.,
LVL 1
marrowyungAsked:
Who is Participating?
 
Eugene ZConnect With a Mentor Commented:
there are several way do it: for sql jobs you may set msdb sysjobs table trigger for insert delete  update for example from
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4766e531-8edb-4c40-a04f-eab1bdc86014/how-to-audit-who-changed-a-sql-job
create trigger trigg on msdb.dbo.sysjobs 
 after insert , update 
 as 
 EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'name_of_mail_profile',
     @recipients = 'aaaa@ad.com',
     @body = 'New job creation or job modification alert',
     @subject = 'Automated Message' ;

Open in new window

---------------------------------------------------------
for the rest ... start from:
Auditing in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd392015(SQL.100).aspx

and
SQL Server DDL Triggers to Track All Database Changes
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

Keeping your SQL Server DDL Trigger Audit Table in Check
http://www.mssqltips.com/sqlservertip/2101/keeping-your-sql-server-ddl-trigger-audit-table-in-check/
0
 
marrowyungAuthor Commented:
so we can do it from SQL server SSMS UI ?  I remember that an indian show us this by using UI without any script .
0
 
marrowyungAuthor Commented:
all solution above seems don't include the sending email as an alert, how come ?
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
marrowyungAuthor Commented:
this workable for SQL server 2005 with SP2?
0
 
Eugene ZCommented:
yes, you can use SSMS GUI for some of your audit needs : see security-> audit-> new audit ... etc

you can use SSMS to run code
see an another "step-by-step"
http://sqlknowledge.com/2010/06/audit-databases-using-ddl-triggers/

<all solution above seems don't include the sending email as an alert, how come ? >

see my above posted "jobs alert" code-example with email
also check the above posted links
specially "Auditing in SQL Server 2008"
http://technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
<this workable for SQL server 2005 with SP2? >
DDL audit trg started from sql 2008
0
 
marrowyungAuthor Commented:
one thing is, when i remove the DDL trigger, any email alert will send out and say someone removed it ?

but I am sure that when adding it back, no email alert will happen !
0
 
marrowyungAuthor Commented:
"<this workable for SQL server 2005 with SP2? >
 DDL audit trg started from sql 2008 "

oh, then what kind of solution can be done for SQL 2005 ?
0
 
marrowyungAuthor Commented:
so remove trigger will be:

delete trigger trigg on msdb.dbo.sysjobs 
 after insert , update 

Open in new window


?
0
 
marrowyungAuthor Commented:
by this link:
http://technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx

"While SQL Trace can be used to satisfy many auditing needs", what information can be return from Trace? that one is the trace result by SQL profiler, right?

SQL profiler can capture only login and logout.

but in SQL server 2005, it said "Login triggers, server triggers, and DDL triggers were available to do custom auditing of specific types of events"

how it can be done in SQL 2005,  any step by step guide ?

also for the trigger in your example, any way to cusotmize the audit message to include what job, login has been created with detail about that ?

one very important thing before I follow the rest on this linke http://technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx is, are all these need SQL server 2008 R2 ENTERPRISE Edition?

As you know Audit is the killing feature of the entperprise edition.

from that link:

"SQL Server 2008 Enterprise enhances the auditing capabilities of the server by introducing native auditing functionality into the database engine. The new SQL Server Audit feature maintains all the capabilities of the SQL Server 2005 auditing solutions and provides enhancements such as flexibility in audit data targets and granular auditing."

or Enterprise edition just enchance it further by introducing native auditing functionality into the database engine?

All codes from that link can be setup and configure  in SQL server 2008 R2 standard edition without any trouble?

you know, CC audit is not allowed in standard edition.

one of the example is:

CREATE SERVER AUDIT PCI_Audit
TO FILE (FILEPATH = ’F:\AuditLogs\’, MAXSIZE = 1GB, MAX_ROLLOVER_FILES = 80)
WITH (QUEUE_DELAY = 0, ON_FAILURE = SHUTDOWN)

Open in new window


this can be done by UI ?

hwo about this :

CREATE SERVER AUDIT SPECIFICATION PCI_Server_Mgmt_Spec
FOR SERVER AUDIT PCI_Audit
  ADD (BACKUP_RESTORE_GROUP),
  ADD (SERVER_STATE_CHANGE_GROUP),
  ADD (SERVER_ROLE_MEMBERSHIP_CHANGE_GROUP)
WITH (STATE = OFF)

Open in new window


?

no need to run that manually ?
0
 
marrowyungAuthor Commented:
I can tell you that from UI, it will say that audit can't be created, by script:

USE [master]

GO

CREATE SERVER AUDIT [Audit-20141211-081458]
TO FILE 
(	FILEPATH = N'E:\Auditlog'
	,MAXSIZE = 20 MB
	,MAX_ROLLOVER_FILES = 10
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)

GO

Open in new window


OR

CREATE SERVER AUDIT PCI_Audit
TO FILE (FILEPATH = 'E:\Auditlog\', MAXSIZE = 1GB, MAX_ROLLOVER_FILES = 80)
WITH (QUEUE_DELAY = 1000, ON_FAILURE =CONTINUE)

Open in new window


it will tells you that:

Msg 33075, Level 16, State 1, Line 2
Auditing is not available in this edition of SQL Server. For more information about feature support in the editions of SQL Server, see SQL Server Books Online.

Open in new window

0
 
marrowyungAuthor Commented:
so what kind of audit can be done on standard edition, that's why I typed this in my question !
0
 
marrowyungAuthor Commented:
I tried to create a trigger on msdb.dbo.sysjobs , it has complian message:

Msg 2108, Level 15, State 1, Procedure trigg, Line 5
Cannot create trigger on 'msdb.dbo.sysjobs' as the target is not in the current database.

what is that mean? I works for one MS SQL server but not the other one, any idea?
0
 
Eugene ZConnect With a Mentor Commented:
try to use:

use msdb
go

create trigger trigg on dbo.sysjobs
 after insert , update
 as
 EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'name_of_mail_profile',
     @recipients = 'aaaa@ad.com',
     @body = 'New job creation or job modification alert',
     @subject = 'Automated Message' ;
0
 
marrowyungAuthor Commented:
it is funny that yesterday I tried that,  it will complain no statement before create trigger statement, which is the use msdb statement.

so trigger statement is :

    DROP TRIGGER trigg;
GO

?
0
 
marrowyungAuthor Commented:
and so if I want any delete of SP detected too, I should do:

use msdb
 go
 
create trigger trigg on dbo.sysjobs 
  after insert , update , delete
  as 
  
  EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'name_of_mail_profile',
      @recipients = 'aaaa@ad.com',
      @body = 'New job creation or job modification alert',
      @subject = 'Automated Message' ;

Open in new window


?

anyway to add extra infromatoin like:
1) who create that.
2) when it create that
3) the name of the SP
4) script of the SP ?
0
 
marrowyungAuthor Commented:
Dear all,

right now the follow was left unansewred:

1) what login has been created and who create it !
 2) What login has been deleted and who delete it!

any suggestion ?
0
 
Eugene ZCommented:
I'd suggest to buy 3rd party Audit tools
for example: Idera  sqlcompliancemanager
https://www.idera.com/productssolutions/sqlserver/sqlcompliancemanager/overview


or use above posted solution " via the new Auditing features by using a Server Audit Specification on the SERVER_PRINCIPAL_CHANGE_GROUP."
more
http://msdn.microsoft.com/en-us/library/cc280663.aspx

also see this example


Catching who dropped SQL Server logins
http://blogs.msdn.com/b/askjay/archive/2010/10/01/catching-who-dropped-sql-server-logins.aspx
0
 
marrowyungAuthor Commented:
"or use above posted solution " via the new Auditing features by using a Server Audit Specification on the SERVER_PRINCIPAL_CHANGE_GROUP."
 more
http://msdn.microsoft.com/en-us/library/cc280663.aspx"

the new auditing feature only avaible for Enterprise edition and we only have standard edition, that's why I type it in the question .
0
 
marrowyungAuthor Commented:
"Catching who dropped SQL Server logins
http://blogs.msdn.com/b/askjay/archive/2010/10/01/catching-who-dropped-sql-server-logins.aspx "

by that link, I can create:

create trigger [no_dropped_logins] 
on all server 
for add_login

Open in new window

too ?

EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS 'CmdText' 

Open in new window


That one means the whole command to drop that login?

One concern is the why we need a temporary table instead of a permanent one, and if the temporary table is gone, we can't drop login anymore as the drop request will rollback.

the insert trigger can also do create this too and we can' insert login anymore if temporary table is gone.

it seems doesn't make any sense, right?
0
 
marrowyungAuthor Commented:
from that link:

"You can of course easily create a permanent table for use with this, but we only needed this trigger for very short period of time "

I need it for a long period of time as long as the SQL server is up and running.

so creating the add_login trigger will prevent the SQL server from adding login if the temporary table was gone or the disk use to store that temporary table is full  ?
0
 
marrowyungAuthor Commented:
did you ever try to use the SQL server 2008 R2 and after's enterprise edition and see if the server audti feature can help on this ?
0
All Courses

From novice to tech pro — start learning today.