28 November 2018

Monitoring Changes in Permissions, Users, Roles and Logins

by Redgate Monitor Team

7,396 0 Security

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

Reports the number of changes to logins, users, roles in the past 10 minutes. It measures every change but doesn’t tell you if this is a drift from what it should be, since it might only be a permission being temporarily created and then revoked.

First, create the following function in the database on the monitored SQL Server instance. It uses the default trace:

IF Object_Id('dbo.SeeAccessControlChanges') IS NOT NULL
     DROP function dbo.SeeAccessControlChanges
  GO
  CREATE FUNCTION dbo.SeeAccessControlChanges
  /**
  Summary: >
    This function gives you a list
    of security events concerning users, roles and logins
    from the default trace
  Author: Phil Factor
  Date: 04/10/2018
  Examples:
     - Select * from dbo.SeeAccessControlChanges(DateAdd(day,-1,SysDateTime()),SysDateTime())
  columns: datetime_local, action, data, hostname, ApplicationName, LoginName, traceName, spid, EventClass, objectName, rolename, TargetLoginName, category_id, ObjectType 
  Returns: >
        datetime_local datetime2(7)
        action nvarchar(816)
        data ntext
        hostname nvarchar(256)
        ApplicationName nvarchar(256)
        LoginName nvarchar(256)
        traceName nvarchar(128)
        spid int
        EventClass int
        objectName nvarchar(256)
        rolename nvarchar(256)
        TargetLoginName nvarchar(256)
        category_id smallint
        ObjectType nvarchar(128)
          **/
    (
    @Start DATETIME2,--the start of the period
    @finish DATETIME2--the end of the period
    )
  RETURNS TABLE
   --WITH ENCRYPTION|SCHEMABINDING, ..
  AS
  RETURN
    (
    SELECT 
        Convert(--correct the date for local time.
          DATETIME2, 
          SwitchOffset(StartTime, DateName(TzOffset, SysDateTimeOffset()))
               ) AS datetime_local, 'User '+Coalesce( LoginName+ ' ','unknown ')+ 
        CASE EventSubclass --interpret the subclass for these traces
          WHEN 1 THEN 'added ' WHEN 2 THEN 'dropped ' WHEN 3 THEN 'granted database access for ' 
          WHEN 4 THEN 'revoked database access from ' ELSE 'did something to ' END+ Coalesce(TargetLoginName,'') 
        + Coalesce( CASE EventSubclass WHEN 1 THEN ' to object ' ELSE ' from object ' end+objectname, '') AS action,
        Coalesce(TextData,'') AS [data], hostname, ApplicationName, LoginName, TE.name AS traceName, spid, 
        EventClass, objectName, rolename, TargetLoginName, TE.category_id, 
      SysTSV.subclass_name AS ObjectType
       FROM::fn_trace_gettable(--just use the latest trace
           (SELECT TOP 1 traces.path FROM sys.traces 
              WHERE traces.is_default = 1), DEFAULT) AS DT
        LEFT OUTER JOIN sys.trace_events AS TE
          ON DT.EventClass = TE.trace_event_id
        LEFT OUTER JOIN sys.trace_subclass_values AS SysTSV
          ON DT.EventClass = SysTSV.trace_event_id
         AND DT.ObjectType = SysTSV.subclass_value
      WHERE StartTime BETWEEN @start AND @finish
      AND TargetLoginName IS NOT NULL
    )
  GO

IF Object_Id('dbo.SeeAccessControlChanges') IS NOT NULL

DROP function dbo.SeeAccessControlChanges

CREATE FUNCTION dbo.SeeAccessControlChanges

/**

Summary: >

This function gives you a list

of security events concerning users, roles and logins

from the default trace

Author: Phil Factor

Date: 04/10/2018

Examples:

- Select * from dbo.SeeAccessControlChanges(DateAdd(day,-1,SysDateTime()),SysDateTime())

columns: datetime_local, action, data, hostname, ApplicationName, LoginName, traceName, spid, EventClass, objectName, rolename, TargetLoginName, category_id, ObjectType

Returns: >

datetime_local datetime2(7)

action nvarchar(816)

data ntext

hostname nvarchar(256)

ApplicationName nvarchar(256)

LoginName nvarchar(256)

traceName nvarchar(128)

spid int

EventClass int

objectName nvarchar(256)

rolename nvarchar(256)

TargetLoginName nvarchar(256)

category_id smallint

ObjectType nvarchar(128)

**/

(

@Start DATETIME2,--the start of the period

@finish DATETIME2--the end of the period

)

RETURNS TABLE

--WITH ENCRYPTION|SCHEMABINDING, ..

RETURN

(

SELECT

Convert(--correct the date for local time.

DATETIME2,

SwitchOffset(StartTime, DateName(TzOffset, SysDateTimeOffset()))

) AS datetime_local, 'User '+Coalesce( LoginName+ ' ','unknown ')+

CASE EventSubclass --interpret the subclass for these traces

WHEN 1 THEN 'added ' WHEN 2 THEN 'dropped ' WHEN 3 THEN 'granted database access for '

WHEN 4 THEN 'revoked database access from ' ELSE 'did something to ' END+ Coalesce(TargetLoginName,'')

+ Coalesce( CASE EventSubclass WHEN 1 THEN ' to object ' ELSE ' from object ' end+objectname, '') AS action,

Coalesce(TextData,'') AS [data], hostname, ApplicationName, LoginName, TE.name AS traceName, spid,

EventClass, objectName, rolename, TargetLoginName, TE.category_id,

SysTSV.subclass_name AS ObjectType

FROM::fn_trace_gettable(--just use the latest trace

(SELECT TOP 1 traces.path FROM sys.traces

WHERE traces.is_default = 1), DEFAULT) AS DT

LEFT OUTER JOIN sys.trace_events AS TE

ON DT.EventClass = TE.trace_event_id

LEFT OUTER JOIN sys.trace_subclass_values AS SysTSV

ON DT.EventClass = SysTSV.trace_event_id

AND DT.ObjectType = SysTSV.subclass_value

WHERE StartTime BETWEEN @start AND @finish

AND TargetLoginName IS NOT NULL

)

Metric definition

Name

AccessControlChanges

Description

Reports the number of changes to logins, users, roles in the past 10 minutes. It measures every change but doesn't tell you if this is a drift from what it should be, since it might only be a permission being temporarily created and then revoked.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			SELECT 
    Count(*)
   FROM::fn_trace_gettable(--just use the latest trace
       (SELECT TOP 1 traces.path FROM sys.traces 
          WHERE traces.is_default = 1), DEFAULT) AS DT
    WHERE StartTime > DateAdd (MINUTE, -10,SysDateTime())
  AND TargetLoginName IS NOT NULL
			
				
					
				
					1
2
3
4
5
6
7
				
						SELECT 
    Count(*)
   FROM::fn_trace_gettable(--just use the latest trace
       (SELECT TOP 1 traces.path FROM sys.traces 
          WHERE traces.is_default = 1), DEFAULT) AS DT
    WHERE StartTime > DateAdd (MINUTE, -10,SysDateTime())
  AND TargetLoginName IS NOT NULL

Instances to collect from

default

Databases to collect from

master

Collection frequency

5 mins

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Change to a login, role or user

Description

There has been a change to a security principal. For example, a login has been added or removed, as a server login or database user, or has been added or removed from a fixed server role or database role, or a database.

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:
Medium:
Low:	1

Raise an alert when the threshold is passed for

collections

Alert is

Enabled

7,396 Rate

Click to rate this post!

[Total: 5 Average: 4]

Monitoring Changes in Permissions, Users, Roles and Logins

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics