0

Is it possible to track which user was deleted from SQL Server and when was it deleted?

1
  • 1
    Do you mean user as in Database User? Or user as in SQL/Windows Logon? They're different things. Jul 2 '21 at 4:23
2

If you're talking about detecting when new Server Logins are created and dropped, yes you can do that with server-wide DDL triggers. For example:

drop trigger if exists DDL_Server_Logins on all server;
go
create trigger DDL_Server_Logins
on all server
for create_login, drop_login
as
begin
    declare @event_type sysname, @command nvarchar(max)
    select @event_type = eventdata().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(128)'),
        @command = eventdata().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
    print 'Server Cat is watching (' + @event_type + '): ' + @command;
end
go
create login doobey with password = N'Doobey Doobey Doo!';
go
drop login doobey;
go
drop trigger DDL_Server_Logins on all server;
go

Which outputs the following:

Server Cat is watching (CREATE_LOGIN): create login doobey with password = N'******';
Server Cat is watching (DROP_LOGIN): drop login doobey;

Notes:

  1. It's up to you to record where and when this happens, such as in audit tables.
  2. It's not retrospective - the trigger has to exist (and remain enabled) to capture these events in real time.

References:

1

If you have got audit setup already using any of the below means, you will be able to see when user was deleted.

  • Profiler trace
  • Server level triggers
  • Extended events
  • Monitoring tools
  • any other auditing means

If you don't have any audit setup already, if you are having regular backups, you can find out, which users were deleted, between backups.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.