MSSQL Find WHO Deleted_Created Table

 use Test01

GO

--select * from fn_dblog(null,null)

--Who Created Table

select [Begin Time],[Transaction Name],[Transaction SID] from 

fn_dblog(null,null) where [Transaction Name] = 'CREATE TABLE'

--who Dropped Table

select [Begin Time],[Transaction Name],[Transaction SID] from 

fn_dblog(null,null) where [Transaction Name] = 'DROPOBJ'

--who inserted Data

select [Begin Time],[Transaction Name],[Transaction SID] from 

fn_dblog(null,null) where [Transaction Name] = 'INSERT'

--who Deleted Data

select [Begin Time],[Transaction Name],[Transaction SID] from 

fn_dblog(null,null) where [Transaction Name] = 'DELETE'

--who Updated Data

select [Begin Time],[Transaction Name],[Transaction SID] from 

fn_dblog(null,null) where [Transaction Name] = 'UPDAT



Once got [Transaction SID]

Now RUN following Query:

use master

GO

select SUSER_SNAME(Transaction SID)

Example:

use master

GO

select SUSER_SNAME(0x0105000000000005150000007CEB240DDA16EB2307E53B2BF0BC0100)

Comments

Post a Comment

Popular posts from this blog

SQL Server – How to re-initialize just a single article in transaction replication

  SQL Server – How to re-initialize just a single article in transaction replication -- Upskill 2020 .  1. Turn off @allow_anonymous and @immediate_sync on the publication. use PUBLICATION_DB_NAME go EXEC sp_changepublication @publication = 'Upskill2020_Test', @property = N'allow_anonymous', @value = 'false' GO   EXEC sp_changepublication @publication = 'Upskill2020_Test', @property = N'immediate_sync', @value = 'false' GO The reason we must disable @immediate_sync is because every time you add a new article, and if @immediate_sync is  enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article. 2. Add new article. EXEC sp_addarticle @publication = 'Upskill2020_Test', @article = 'Employee', @source_object = 'Employee', @force_invalidate_snapshot = 1 3. Refresh the subscription EXEC sp_refreshsubscriptions @publication = 'Repl_RBD' GO 4. Check the current sna...
Read more

SQL Deadlock Report - Extended Events

  SQL Deadlock Report - Extended Events Event Creation create EVENT SESSION EE_Deadlock ON SERVER     ADD EVENT sqlserver.xml_deadlock_report     ADD TARGET package0.ring_buffer     (SET max_memory = 4096) WITH (MAX_DISPATCH_LATENCY = 30 SECONDS); create Procedure  DeadlockReport as Begin alter event session EE_Deadlock ON SERVER state=Stop; if not exists (select * from Archival.sys.objects where name like '%Deadlock_Data%') Begin Create TABLE Archival..Deadlock_Data (     Rowid  INT IDENTITY PRIMARY KEY,     event_data XML) End Truncate Table Archival..Deadlock_Data insert into Archival..Deadlock_Data(event_data) ( select cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph FROM (select CAST(target_data as xml) as TargetData from sys.dm_xe_session_targets st join sys.dm_xe_sessions s on s.address = st.event_session_address where name = 'EE_Deadlock') AS Data CROSS APPLY TargetData.nodes...
Read more

DBA Daily Commands in Handy

Image
sp_who2 active:  View details of active processes on SQL Server Engine DBCC INPUTBUFFER(session id):  View the current query being executed by a specific proc ess Check the open transaction in SQL Server: DBCC OPENTRAN Check the blocked process: SELECT * FROM sysprocesses where blocked > 0 and spid = 23; select * from sys.sysprocesses where blocked != 0; Check the lock information: SELECT * from sys.dm_tran_lock Check the connection information from view in SQL Server: SELECT * FROM sys.dm_exec_sessions; Check the database files location, size, and max size in SQL Server SELECT * FROM sysaltfiles Check the collation Setting on the SQL Server SP_HELPSORT Check the log Usage on SQL Server databases select * from sys.databases where name like '%log_reuse_wait_desc%'
Read more