Link to home
Create AccountLog in
Avatar of marrowyung
marrowyung

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of marrowyung
marrowyung

ASKER

thansk.
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 ?
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 ?
>> 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()
ok.
do you know if the vesrion for SQL 2012 can use for any server running SQL server 2008 R2?
I've tried creating this trigger in SQL Server 2008 R2 and it works fine..
ok, thansk.
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 ?
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
rrjegan17,

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

how can I customerizes the email message ?
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