SQL AlwaysOn - Audit for new logins that are created and alert the DB Admins

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  December 8, 2016 at 2:26 pm

  #316622

  Hello,

  Is there anyway to track new logins those are created in a node and need to be created in other node.

  If I run the Microsoft script (https://support.microsoft.com/en-us/kb/918992) it will list me all the accounts which needs to be transferred to the new node. But how to find the different login accounts which are missing in Node 2?

  Thanks.

  Thanks.

• Jim Youmans-439383

  SSCommitted

  Points: 1657

  More actions

  December 21, 2016 at 2:14 pm

  #1919238

  For the below, you would have to do this on each server in you AlwaysOn group.

  You can create a DDL trigger to catch and alert you on CREATE_USER and DROP_USER Events.

  [font="Courier New"]

  USE [your_database_name];

  GO

  CREATE TRIGGER CatchUser

  ON DATABASE

  FOR CREATE_USER, DROP_USER

  AS

  BEGIN

  SET NOCOUNT ON;

  DECLARE @x XML = EVENTDATA();

  -- INSERT dbo.LoggingTable(Columns)

  SELECT

  EventType = @x.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(256)'),

  UserName = @x.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(256)'),

  LoginName = @x.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)'),

  StartTime = @x.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');

  END

  [/font]

  You can also use the default trace. You just have to create a job to check it every x minutes.

  [font="Courier New"]

  DECLARE @path NVARCHAR(260);

  SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),

  CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'

  FROM sys.traces WHERE is_default = 1;

  SELECT EventType = CASE EventSubClass WHEN 3 THEN 'CREATE_USER'

  WHEN 4 THEN 'DROP_USER' END, TargetUserName, LoginName, StartTime

  FROM sys.fn_trace_gettable(@path, DEFAULT)

  WHERE EventClass = 109 -- Create DB User Event

  AND DatabaseName = N'your_database_name'

  ORDER BY StartTime DESC;[/font]

  You can have the queries above write to a table and then check the table for new entries and send an email or however you want to handle it.