Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

database audit for object access

SQL Server v2014.  What's the easiest way to send an email  notification when a particular user reads from a view?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Just for curiosity, why do you need to notificate when a SELECT occurs?
There's no trigger for SELECT operations, as far as I know.
You can always try the AUDIT feature but if the idea is to not allow an user to perform the SELECT in a View just DENY the user that permission.
Avatar of dbaSQL

ASKER

No.  The idea is not to deny a user from SELECTing from a view.  The idea is simply to know when a particular user is reading from a view.
I'm afraid that can only be achieved by enabling Auditing.
There will be some overhead, though.
Avatar of dbaSQL

ASKER

I find it suprising that this is only available with the Auditing, but be that the case, can you tell me how I would modify the example below to send an email notification if a certain user performs a SELECT on the view?


-- Create the database audit specification.  
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables  
FOR SERVER AUDIT Payrole_Security_Audit  
ADD (SELECT , INSERT  
     ON HumanResources.EmployeePayHistory BY dbo )  
WITH (STATE = ON) ;  
GO
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL

ASKER

Again, very surprised that there is not another means of handling this, but it is what it is.  Thank you, Vitor.