MS SQL change alert.

Dear all,

Any way to make MS SQL server send an email alert (by operators) if any database structure change and what it is ?

Any third party tools needs for this ?

DBA100.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Yes, you can do it via DDL Triggers.
Please go through one of a sample trigger to capture all DDL changes in the database.

http://blog.extreme-advice.com/2012/10/25/capture-schema-change-in-sql-server-to-maintain-history/
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
thansk.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
For this:

"DDL_DATABASE_LEVEL_EVENTS"

What it includes ?

"[HostName] [varchar](50) NULL"

What is the hostname is about ? the hostname the change initial from ?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
marrowyungSenior Technical architecture (Data)Author Commented:
Anyway to modify the information the trigger will add?  anyway to add more ?

"
[SystemUser] [varchar](100) NULL,

[CurrentUser] [varchar](100) NULL,

[OriginalUser] [varchar](100) NULL,
"
in what situation the 3 x users different ?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> What is the hostname is about ? the hostname the change initial from ?

Yes, it is..

>> in what situation the 3 x users different ?

It will have difference if the login name and user name are different.
Kindly try running this for your SQL user session.

select SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
do you know if the vesrion for SQL 2012 can use for any server running SQL server 2008 R2?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
I've tried creating this trigger in SQL Server 2008 R2 and it works fine..
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok, thansk.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
do you have the SQL server 2005 try from their old article? Any difference in result ? it seems that the version for SQL 2005 is providing much limit information to return ?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes, SQL Server 2005 has limited set of events available for monitoring and the full set of events below for your reference:

SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms189871%28v=sql.90%29.aspx

SQL Server 2012
http://msdn.microsoft.com/en-us/library/bb522542.aspx
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
rrjegan17,

it seems that the email format and email action is not here, right?

how can I customerizes the email message ?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You can use msdb.dbo.sp_send_dbmail procedure to send emails in the format you require.
More info about the procedure along with examples below:

http://msdn.microsoft.com/en-us/library/ms190307.aspx
0
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.

All Courses

From novice to tech pro — start learning today.