How to rollback changes made to the database by a specific user?

In mssql, is it possible to rollback changes made by a specific user to the database?
For example if that user ran an update query and did not write begin transaction before, is it possible to revert changes made by his query WITHOUT restoring the entire Database (I want to preserve changes made by other users)?

It would be difficult to do that using Native tools or you will have to look for third party tools.

Natively:

• Default trace

Can be used to track Object Altered, Object Created and Object Deleted along with other stuff. Refer to the link below.

Refer to : The default trace in SQL Server - the power of performance and security auditing

Note this is very limited as it will just give you info related to who did what e.g. Who dropped the table, etc.

• fn_dblog function ==> [undocumented]

Note: You need to transaction log backups available or the transaction should not have been cleared from the active portion of the log

Excellent reading at : Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN and
Joining sys.dm_tran_database_transactions to fn_dblog Results

• Third Party tools - the best one out is ApexSQL Log

As a side note:


I want to preserve changes made by other users

Depending on your version of sql server and your business requirements, you can implement

• Change Data Capture

• Change Tracking

• Run SQL Server Audit

• Server-Side Tracing and Collection - with appropriate events selected.

StackExchange Database Administrators Q#53495, answer score: 3