Solved

MS SQL change alert.

Posted on 2013-05-21
13
297 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now