Solved

MS SQL change alert.

Posted on 2013-05-21
13
303 Views
Last Modified: 2013-07-05
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
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 39183623
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39183862
thansk.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183875
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:marrowyung
ID: 39183884
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39183909
>> 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
 
LVL 1

Author Comment

by:marrowyung
ID: 39183918
ok.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183923
do you know if the vesrion for SQL 2012 can use for any server running SQL server 2008 R2?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39183931
I've tried creating this trigger in SQL Server 2008 R2 and it works fine..
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183963
ok, thansk.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39186672
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39186689
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39301559
rrjegan17,

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

how can I customerizes the email message ?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39301758
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 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