Trace the SPID of Login in SQL Server

Tracing the Login name for the SPID in SQL Server

We do often face the scenarios where some object might have modified/updated/dropped/deleted by the user/admin knowingly or unknowingly.

Human errors are always possible and we cannot eradicate the human errors for 100%.

But we can trace who has done it.

Options to trace

1. Reading the LDF

http://manage-sqlserver.blogspot.in/2012/08/read-ldf-for-particular-transaction-in.html

2. Reading the backup file if backup happened after the wrong done

http://manage-sqlserver.blogspot.in/2012/08/read-backup-file-of-sql-server.html

3. Easy option is querying the existing trace files (default traces of SQL Server)
http://manage-sqlserver.blogspot.in/2012/08/default-trace-in-sql-server.html

By using all the above options we will get the SPID who has done the operation on table/database/object.

To find who has logged in with that SPID, we have to prepare before disaster happen by enabling the trace flag in all important production databases.

DBCC TRACEON (4013,-1)

Above Trace flag will capture the SPID of the login also in SQL Server Error log as shown below

2012-08-15 01:46:49.320 spid53 Login: sa MYCOMPUTER, server process ID (SPID): 53, kernel process ID (KPID): 53.

Hope the above information helps

Thanks