Solved

turn on DDL trigger for SQL server 2008 R2 stnadared

Posted on 2014-12-09
21
301 Views
Last Modified: 2015-02-05
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.,
0
Comment
Question by:marrowyung
  • 17
  • 4
21 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 40490370
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40490520
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40490587
all solution above seems don't include the sending email as an alert, how come ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40490772
this workable for SQL server 2005 with SP2?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 40492902
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40493071
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40493072
"<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
 
LVL 1

Author Comment

by:marrowyung
ID: 40493073
so remove trigger will be:

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

Open in new window


?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40493135
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40493296
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:marrowyung
ID: 40493297
so what kind of audit can be done on standard edition, that's why I typed this in my question !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40493338
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
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 500 total points
ID: 40495010
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40495467
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40495474
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40495492
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 40496306
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40499826
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 40499850
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 40499874
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40499876
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

707 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

14 Experts available now in Live!

Get 1:1 Help Now