How to set up a report when a specific user makes changes to a database

Hi,
We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
2003 R2 SP2 server.
I successfully created a mailbox for SQL to use and associated the SQL Agent
to use this e-mail when sending out alerts.
I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to a database.
Can somebody please point me in the right direction.
Thank you,
WarrenCan you explain exactly what you mean by "makes a change to the database"? A
LTER TABLE? UPDATE?
Something else? Triggers and possibly event notifications seems to be what y
ou want to look into.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2
003 R2 SP2 server.
> I successfully created a mailbox for SQL to use and associated the SQL Age
nt to use this e-mail
> when sending out alerts.
> I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to
> a database.
> Can somebody please point me in the right direction.
> Thank you,
> Warren
>|||Basically anything that would be considered a change to the database for
auditing purposes.
Can you lead me in the right direction on setting up triggers or event
notifications? I am new to SQL 2005.
Thank you,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Can you explain exactly what you mean by "makes a change to the database"?
> ALTER TABLE? UPDATE? Something else? Triggers and possibly event
> notifications seems to be what you want to look into.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>|||> Basically anything that would be considered a change to the database for a
uditing purposes.
Wow. Are you willing to pay the performance penalty for this?
Your best bet is probably to use some of the products out there. They use a
combination of
server-side Profiler traces along with reading the transaction log (somethin
g which we cannot do
ourselves) so performance impact would be less than if doing it ourselves. N
ot to mention the dev
time of doing it yourself.
Here's one: http://www.lumigent.com/products/auditdb.html
Some of the other log reader tools vendors might also have such products, I'
ve listed the ones I
know of on my links page: http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Basically anything that would be considered a change to the database for a
uditing purposes.
> Can you lead me in the right direction on setting up triggers or event not
ifications? I am new to
> SQL 2005.
> Thank you,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>|||Maybe I should refrase that a little bit.
Basically I need to have some type of checks and balances setup where an
alert is sent when the dba does something to a database or makes changes.
In the configuration we are using the dba may connect to the SQL server once
a quarter if that.
Warren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Wow. Are you willing to pay the performance penalty for this?
> Your best bet is probably to use some of the products out there. They use
> a combination of server-side Profiler traces along with reading the
> transaction log (something which we cannot do ourselves) so performance
> impact would be less than if doing it ourselves. Not to mention the dev
> time of doing it yourself.
> Here's one: http://www.lumigent.com/products/auditdb.html
> Some of the other log reader tools vendors might also have such products,
> I've listed the ones I know of on my links page:
> http://www.karaszi.com/SQLServer/links.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>|||Did you look at the product?
There's no pre-packaged solution for this. You need to determine exactly wha
t type of changes you
want to be alerted for. Based on that decide whether you find a product that
suit your need or if
you want to do it yourself. Depending on what type of changes you want to be
alerted for, event
notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> Maybe I should refrase that a little bit.
> Basically I need to have some type of checks and balances setup where an a
lert is sent when the
> dba does something to a database or makes changes.
> In the configuration we are using the dba may connect to the SQL server on
ce a quarter if that.
> Warren
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>|||Okay,
Maybe I really just need the basic and I can't figure out how to make it
work. Books on line wasn't very helpful.
Is there a white paper on setting up user auditing for just logging on?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Did you look at the product?
> There's no pre-packaged solution for this. You need to determine exactly
> what type of changes you want to be alerted for. Based on that decide
> whether you find a product that suit your need or if you want to do it
> yourself. Depending on what type of changes you want to be alerted for,
> event notification can be an option. See Books Online, CREATE EVENT
> NOTIFICATION.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>|||> Is there a white paper on setting up user auditing for just logging on?
Only login events. That makes it a very special case, and you can do that w
ith a few clicks. SSMS,
Right-click the server in Object Explorer, Properties, the security window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:%23OOnP485GHA.512@.TK2MSFTNGP06.phx.gbl...
> Okay,
> Maybe I really just need the basic and I can't figure out how to make it w
ork. Books on line
> wasn't very helpful.
> Is there a white paper on setting up user auditing for just logging on?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
>