How to list all past remote login information

Question

Instead of using "Remote Desktop" for connecting to SQL Server, I and my co-workers are using SQL Server Management Studio to apply changes to the remote server (each user has their own credentials).

One of us made some changes on SQL tables that put all the data at risk. I'm pretty sure one of us is guilty. My question is this. How can I prove this? Is there any sort of query to show all past remote connections from a client to a remote SQL Server instance? Maybe with IP addresses, computer names, credentials.

Does SQL Server Management Studio have some sort of log file or any query to show remote server logins?

P.S. No full backup on remote the SQL Server instance. Logs are pretty clean.

This is how we login to SQL Server:

Answer 1

You can login with (IP,Port Number) to remote server.By default the SQL Server don't log the logins.

If you have pretty clean logs then you shall not get login history data. if you have configure SQL Login Audit before pretty clean log. Then some point of time there will be changes that to get the 'SQL Login Audit' details through TSQL like

EXEC xp_readerrorlog; 

It will give only that 'SQL login audit details' which you had configure SSMS. like

1) Failed logins only
2)Successful logins only
3)Both Failed and successful logins

As per MSDN BOL https://msdn.microsoft.com/en-us/library/ms176013.aspx For remote server login details of current user you can find out from TSQL.

SELECT login_name ,COUNT(session_id) AS session_count 
FROM sys.dm_exec_sessions 
GROUP BY login_name;

for ref https://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/ and https://msdn.microsoft.com/en-us/library/cc280728.aspx

Answer 2

Short answer is you can’t see historical data for updates unless you already had some auditing system in place at the time change happened.

What you can do is to try reading transaction log using some third party tool like ApexSQL Log and checking if there is info like this written somewhere in t. log.

I know there is a lot of historical data written in transaction log that can be used for auditing but I’m not 100% sure about finding username.

If you want to setup a system that will track this going forward you can try DDL triggers, SQL Traces and more…

The best way to do this would be to use extended events to capture logins so that you know precisely who is connecting to what. You can also add extended events to capture what queries they're running. But, you have to have it set up in order to capture this. Nothing built-in to the system automatically captures this type of information. However, you do have the option of looking at the cache to see what queries have been run, but not who ran them. You can look at sys.dm_exec_query_stats to see an aggregate of attributes about queries that are currently in cache. This won't do what you need, but it's an additional investigative tool you can use until you get the extended events set up.

Also, if luck favours you, you can try below steps:

1. Loginto the instance.
2. Go to security option
3. Expand and right click on Logins
4. click reports> Standard reports > Login statistics

Again a point to note, This report shows some information about logins, but it does not show historical information. Only the currently logged-in users are shown.