DBANoobie (12/6/2013)
Hello all-I was recommended to this site for all the wealth of knowledge and I was hoping ya'll could assist me with this. I am new to the DBA world (only 2 months in) and have been granted the power of the "accidental DBA" you could say.
I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
Any help would be very much appreciated and I apologize if I seem to be too noobish.
not possible, sorry; changes to data are not tracked; after the fact, there's basically no way to get the information.
I don't want to get your hopes up, because it's difficult, and probably not worth your time digging into it.
you can find out WHEN something occurred, but not by WHO; whodunnit information is never tracked in the log.
there is the possibility of reading When it occurred, and infer who did it(maybe) in the transaction log. (whether yourself via sys.fn_dblog() or via a third party tool)
That's only possible if:
1. the database in question is in FULL recovery mode
2. A FULL BACKUP has been taken prior to the event in question.
I've got a query to read the log for specific table objects, but never for a role or user.
just looking at the code, i don't see how to modify it for trying to track changes to roles; i'd think that since sys.roles, sys.users are system views, it will be exceptionally difficult, if not impossible.
SELECT
[PAGE ID],
[Slot ID],
[AllocUnitId],
[Transaction ID],
[RowLog Contents 0],
[RowLog Contents 1],
[RowLog Contents 3],
[RowLog Contents 4],
[Log Record]
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitId IN (SELECT
[Allocation_unit_id]
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions
ON ( allocunits.type IN ( 1, 3 )
AND partitions.hobt_id = allocunits.container_id )
OR ( allocunits.type = 2
AND partitions.partition_id = allocunits.container_id )
WHERE object_id = object_ID('' + 'dbo.GMACTDISASTER' + ''))
AND Operation IN ( 'LOP_MODIFY_ROW', 'LOP_MODIFY_COLUMNS' )
AND [Context] IN ( 'LCX_HEAP', 'LCX_CLUSTERED' )
Lowell