Database audits - permission changes

Question

Database audits - permission changes

icester

Ten Centuries

Points: 1093
More actions
October 2, 2018 at 8:03 am

#364297

Hello SSC,
I am needing to track all user permission changes at the database level. The method I am trying to use is setting up an Audit to track these changes, but I am not able to get this to work. These are the steps I'm using to get this set up:
1) Create Audit at the instance level (Instance --> Security --> Audits)
2) Create Database Audit Specification at the database level (Instance --> Database --> Security --> Database Audit Specifications)
3) Add "DATABASE_PERMISSION_CHANGE_GROUP" Audit Action Type to the Database Audit Specification
4) Enable both, the Audit & Database Audit Specification
5) Test a permission changes with my sysadmin account, test permission change with a db_owner
Nothing gets logged.
6) I then tried adding the SELECT Audit Action Type to the Database Audit Specs and ran a SELECT statement. The Audit DID log this query
I know that the Audit itself works since it was able to log the SELECT query, but for whatever reason I cannot get it to log any database user permission changes. Do you have any guesses at what I might be doing wrong?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 1

icester - Tuesday, October 2, 2018 8:03 AM
Hello SSC,
I am needing to track all user permission changes at the database level. The method I am trying to use is setting up an Audit to track these changes, but I am not able to get this to work. These are the steps I'm using to get this set up:
1) Create Audit at the instance level (Instance --> Security --> Audits)
2) Create Database Audit Specification at the database level (Instance --> Database --> Security --> Database Audit Specifications)
3) Add "DATABASE_PERMISSION_CHANGE_GROUP" Audit Action Type to the Database Audit Specification
4) Enable both, the Audit & Database Audit Specification
5) Test a permission changes with my sysadmin account, test permission change with a db_owner
Nothing gets logged.
6) I then tried adding the SELECT Audit Action Type to the Database Audit Specs and ran a SELECT statement. The Audit DID log this query
I know that the Audit itself works since it was able to log the SELECT query, but for whatever reason I cannot get it to log any database user permission changes. Do you have any guesses at what I might be doing wrong?
Thanks!

Try with this one.
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP

I'd also recommend using
DATABASE_PRINCIPAL_IMPERSONATION_GROUP
DATABASE_CHANGE_GROUP
DATABASE_PRINCIPAL_CHANGE_GROUP

Greetings.

goher2000 SSCertifiable Points: 5479 More actions · Answer 2

audit is one thing, I get alert right away when permission are changed. Just setup WMI alerts and don't forget to setup operator and mail.

USE [msdb]
GO

/****** Object: Alert [Database Role Membership Change] Script Date: 10/2/2018 2:34:46 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: 10/2/2018 2:35:08 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

icester Ten Centuries Points: 1093 More actions · Answer 3

Thanks Alejandro. After further trial/error I found that it was DATABASE_ROLE_MEMBER_CHANGE_GROUP that logged the permission changes (dummy mistake on my part). I've also added the ones you suggested just in case they happen as well. Thanks for your help.

Dan

icester Ten Centuries Points: 1093 More actions · Answer 4

icester

Ten Centuries

Points: 1093

October 2, 2018 at 1:12 pm

#2007781

Thanks goher2000, that'll be handy