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
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author Commented:
all solution above seems don't include the sending email as an alert, how come ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author Commented:
so remove trigger will be:

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

Open in new window


?
0
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author Commented:
so what kind of audit can be done on standard edition, that's why I typed this in my question !
0
marrowyungSenior Technical architecture (Data)Author 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 ZCommented:
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
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author 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
marrowyungSenior Technical architecture (Data)Author 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.