How to Find Who Updated Rows in Table X?

SQL Server Administration
1

Hi, this is on SQL Server 2008.

Someone changed or disabled database role or the login itself.
I checked he built-in reports for Schema Changes and Configurations Changes. Nothing relates.
Where else can I look?
Thanks

2

You might find it by querying the default trace on the system, but remember that they roll over fairly quickly and only retain 5 files. I'm guessing that's what the Schema change report uses, so if it didn't show there...

The best way to monitor these kinds of things is an audit:

Note that SQL Server 2008 won't audit everything unless you're using Enterprise Edition. It also doesn't help you for this particular instance, unless you know there's an audit already in place.

Another option are DDL triggers:

Again, these have to be in place before the drop occurs. The last suggestion I have, assuming your database is in full recovery mode, is to query the log (or log backup files) to find the drop event. MSSQLTips has an article on it:

I'm not sure if DROPOBJ is used for dropping logins, or what would appear for disabling a role.

3

Thanks, @robert_volk. This instance is 2008 Standard. I created a login, then dropped it.The Schema Changes report doesn't reflect either of those changes. I did the same on a 2019 Enterprise instance. The same results, nothing was logged.

4

I have more info @robert_volk. We know which table and which rows were updated. Now need to find which user did it.

5

Might want to go with an audit column that captures current_user type of stuff and not rely on traces

6

I agree, @yosiasz. But the task at hand is to identify which user updated a certain boolean column in Table X for hundreds of rows.

I ran SELECT*
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN ('LOP_MODIFY_ROW') and AllocUnitName LIKE('%MyTable%')

but it didn't return any info that related to UserID.