How To Find out Who Deleted the Login from SQL Server - SQL Server DBA Tutorial

In this video you will learn multiple ways to find out who deleted login in SQL Server such as extracting information from SQL Server Default Trace, by creating Login Audit, by creating Login Audit specification and creating your own trace to find out who deleted the login from SQL Server. It also explains the method that are supported in different versions of SQL Server such as SQL Server 2005, 2008, 2012 and 2014.

--1 How to find out who deleted login using Default trace?

SELECT * into #loginstatus
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL12.SQLTEST\MSSQL\Log\log.trc', default)
select * from #loginstatus
where EventSubClass=2

select S.name,L.EventSubClass, L.LoginName,L.TargetLoginName as LoginDropped,L.StartTime, L.DatabaseID from #Loginstatus L
    inner join sys.trace_events S
    on L.EventClass = S.trace_event_id
    where L.EventSubClass=2 and S.name='Audit Addlogin Event'
    order by StartTime desc

   
--Audit Server Principal Management Event

--2- How to find out who deleted login using SQL Server Audit Trigger

-- 2a) Creating Master Audit Table
 USE [master]

GO


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[LoginAudit](

[Eventtime] [datetime] NULL,

[Eventtype] [nvarchar](100) NULL,

[ServerLogin] [nvarchar](100) NOT NULL,

[DatabaseUser] [nvarchar](100) NOT NULL,

[TSQLText] [varchar](max) NULL,

[Eventdata] [xml] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[LoginAudit] ADD DEFAULT (getdate()) FOR [Eventtime]

GO


-- 2b) Creating Audit Trigger

USE [master]
GO

/****** Object:  DdlTrigger [LoginAudit]    Script Date: 3/31/2015 4:58:33 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [LoginAudit]

ON ALL SERVER

FOR CREATE_LOGIN ,ALTER_LOGIN, DROP_LOGIN

AS

BEGIN

DECLARE @Eventdata XML

SET @Eventdata = EVENTDATA()

IF EXISTS(SELECT 1 FROM master.information_schema.tables WHERE table_name = 'LoginAudit')

Begin

INSERT into master.dbo.LoginAudit

(EventType,EventData, ServerLogin,DatabaseUser,TSQLText)

VALUES (@Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'NVarChar(100)'),

@Eventdata,system_USER,CONVERT(NVarChar(100), CURRENT_USER),

@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVarChar(2000)' ))

End

END


GO

ENABLE TRIGGER [LoginAudit] ON ALL SERVER
GO

--3-  How to find out how deleted Login using Audit and Audit Specification

 -- 3a) Creating Audit
USE [master]
GO

/****** Object:  Audit [LoginAudit]    Script Date: 3/31/2015 5:02:02 PM ******/
CREATE SERVER AUDIT [LoginAudit]
TO FILE
(    FILEPATH = N'C:\Data\'
    ,MAXSIZE = 2048 MB
    ,MAX_ROLLOVER_FILES = 100
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '4248909c-d383-41e9-b1a8-efa47234707d'
)
ALTER SERVER AUDIT [LoginAudit] WITH (STATE = ON)
GO
-- 3b) Creating Audit Specification

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerLoginAuditSpecification]
FOR SERVER AUDIT [LoginAudit]
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON)
GO








--4-  How to find out who deleted the login using by creating your own trace?

/****************************************************/
/* Created by: SQL Server 2014 Profiler          */
/* Date: 03/31/2015  05:41:46 PM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 130, 1, @on
exec sp_trace_setevent @TraceID, 130, 11, @on
exec sp_trace_setevent @TraceID, 130, 12, @on
exec sp_trace_setevent @TraceID, 130, 6, @on
exec sp_trace_setevent @TraceID, 130, 10, @on
exec sp_trace_setevent @TraceID, 130, 14, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - f9ab932d-7665-4aa9-8d39-71715f8523aa'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

How to Find out Who has Dropped the Login from SQL Server - MS SQL Server DBA Tutorial