• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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.
0
marrowyung
Asked:
marrowyung
  • 8
  • 5
1 Solution
 
Raja Jegan RSQL 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
 
marrowyungAuthor Commented:
thansk.
0
 
marrowyungAuthor 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
marrowyungAuthor 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
 
marrowyungAuthor Commented:
ok.
0
 
marrowyungAuthor 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
 
marrowyungAuthor Commented:
ok, thansk.
0
 
marrowyungAuthor 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
 
marrowyungAuthor 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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now