How to get session history in Azure sql server?

1
votes

Someone deleted my azure database tables and procedures. Now I want to know from which workstation/ip this has happened.The person used db owner Id.

Azure portal activity logs don't give any details as deletion is done through sql queries.

I know how to see active sessions in sql service, but I want the history of sessions that existed in last 3 days with my database. Please help!

1
sql-serverazure-sql-databaseazure-sql-server

1 Answers

1
votes

Using the below query can find the history of connection sessions, but it only can shows the local client IP address:

SELECT connection_id, 
       c.client_net_address,
       c.session_id, 
       connect_time,
       client_net_address, 
       client_tcp_port,
       host_name,
       program_name, 
       login_name, 
       row_count
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s ON s.session_id = c.session_id

You will get the results like this: enter image description here

Maybe it can help you know from which workstation/ip this delete operation has happened

Hope this helps.