dbaSQL
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?
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.
There will be some overhead, though.
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.EmployeePay History BY dbo )
WITH (STATE = ON) ;
GO
-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payrole_Security_Audit
ADD (SELECT , INSERT
ON HumanResources.EmployeePay
WITH (STATE = ON) ;
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Again, very surprised that there is not another means of handling this, but it is what it is. Thank you, Vitor.
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.