January 24, 2019 at 8:54 am
Hello All,
Thank you for your service to SQL Server community and looking into my question.
I am currently working on implementing some security measures in my database environments.
Here are the requirements.
1. People with direct access to database can query the tables with sensitive data, If someone selects data from a sensitive table, I would like to get notified. SQL Server Audit can do this, but I would like to know if there is any product in the market that can do this.
2. If there is any change in permissions or roles to any database principal in the database, I would like to get notified.
Thank you for your help.
January 24, 2019 at 8:02 pm
Siva Ramasamy - Thursday, January 24, 2019 8:54 AMHello All,Thank you for your service to SQL Server community and looking into my question.
I am currently working on implementing some security measures in my database environments.
Here are the requirements.
1. People with direct access to database can query the tables with sensitive data, If someone selects data from a sensitive table, I would like to get notified. SQL Server Audit can do this, but I would like to know if there is any product in the market that can do this.
2. If there is any change in permissions or roles to any database principal in the database, I would like to get notified.
Thank you for your help.
One I've used that can alert is Idera's Compliance Manager. They have a free trial period if you want to try it out:
SQL Compliance Manager
Sue
January 25, 2019 at 9:20 am
how about using SQL alerts for notification
USE [msdb]
GO
/****** Object: Alert [Database Role Membership Change] Script Date: 1/24/2019 4:14:39 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'Database Role Membership Change',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@notification_message=N'Role $(ESCAPE_NONE(WMI(RoleName))) was altered for Login $(ESCAPE_NONE(WMI(TargetUserName))) on database $(ESCAPE_NONE(WMI(DatabaseName))) on server $(ESCAPE_NONE(WMI(ComputerName))) by $(ESCAPE_NONE(WMI(LoginName)))',
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM AUDIT_ADD_MEMBER_TO_DB_ROLE_EVENT',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
USE [msdb]
GO
/****** Object: Alert [Server Role Membership Change] Script Date: 1/24/2019 4:15:01 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'Server Role Membership Change',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@notification_message=N'Role $(ESCAPE_NONE(WMI(RoleName))) was altered for Login $(ESCAPE_NONE(WMI(TargetLoginName))) on server $(ESCAPE_NONE(WMI(ComputerName))) by $(ESCAPE_NONE(WMI(LoginName)))',
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply