turn on DDL trigger for SQL server 2008 R2 stnadared

so we can do it from SQL server SSMS UI ?  I remember that an indian show us this by using UI without any script .

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

this workable for SQL server 2005 with SP2?

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

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 !

"<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 ?

so remove trigger will be:

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

Select allOpen in new window

?

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)

Select allOpen 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)

Select allOpen in new window

?

no need to run that manually ?

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

Select allOpen 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)

Select allOpen 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.

Select allOpen in new window

so what kind of audit can be done on standard edition, that's why I typed this in my question !

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?

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

?

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' ;

Select allOpen 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 ?

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 ?

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

"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 .

"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

Select allOpen in new window

too ?

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

Select allOpen 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?

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  ?

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 ?