I’ve answered two such questions recently, one on the Spiceworks forum and one on #sqlhelp on Twitter where I was able to find pertinent information in the transaction log. Key to this was determining that the column [Transaction SID] is the security identifier (SID) of the login/user that performed the action. I can pass that in to the SUSER_SNAME() function to get the user name.
SQL Server likes to use named transactions internally. You can find a lot of useful information just by looking at the [Transaction Name] column. Unfortunately, the names they use are not always the most informative. For example, if you grant, revoke, or deny permissions to a user, you can find a named transaction for each permission change in the transaction log; however, each of those transactions will be named Grant. With a simple query, I can find entries for permission changes, but I cannot tell for sure which of the changes was made.
As I have said above, I used transaction log spelunking to answer two questions recently. The first question was wanting to know who was changing permissions on logins. The second question was asking who was disabling jobs on the server.
The T-Log Solutions
Solution for Finding Who Altered Permissions on Logins
This one was quite simple. I simply look for transactions named CREATE USER, ADD/DROP ROLE MEMBER, or GRANT.
Select SUSER_SNAME([Transaction SID]) As Changer, [Transaction Name]
From fn_dblog(null, null)
Where [Transaction Name] In ('CREATE USER', 'ADD/DROP ROLE MEMBER', 'GRANT');
Solution for Finding Who Altered a Job or a Job Schedule
This one was a little trickier because the name of the transactions were too generic to be relied upon. Changing a job resulted in a transaction named “user_transaction” and changing a schedule yielded a transaction named “update”. I was able to find the transactions I wanted by looking at the AllocUnitName (allocation unit name or name of object allocated a page) column to identify when the clustered index of the dbo.sysjobs and dbo.sysschedules tables were modified. A single transaction can have many log records, and in this case, the log record with the allocation unit name were different than the log record containing the transaction SID. First step is to identify the transactions that modify the desired objects and then query all log records with those transaction IDs.
With TranIDs
As (Select [Transaction ID]
From fn_dblog(null, null)
Where AllocUnitName = 'dbo.sysjobs.clust'
Or AllocUnitName like 'dbo.sysschedules.%')
Select MAX(SUSER_SNAME(DBL.[Transaction SID])) As Changer,
MAX(DBL.AllocUnitName) As AllocationName
From fn_dblog(null, null) DBL
Inner Join TranIDs TI On TI.[Transaction ID] = DBL.[Transaction ID]
Group By DBL.[Transaction ID];
Now, I’m looking for more reasons to dig into the transaction log to see what else I can find.
Ed
Neat use of the system function, here is one use I used it for. If a DBA leaves for exmaple and owns any db’s on the giant farm (which they should not own in the first place) databases and job owners become NULL once the id is disabled at the domain level. So I am the resident scripter of my group and kinda clean it all up.
set nocount on
use master
go
Declare @srvname varchar(55)
Declare @exec_stmt nvarchar(4000)
Select @srvname = @@servername
Print @srvname
DECLARE @name varchar(255), @return int,@original_Owner varchar(60),@owner_sid varchar(50),
@sqltxt varchar(255)
DECLARE c CURSOR
READ_ONLY FOR
Select [name],SUSER_SNAME(sid) from master..sysdatabases
where SUSER_SNAME(sid) is null –and [name] ‘test db’
OPEN c
FETCH NEXT FROM c INTO @name,@owner_sid
WHILE (@@fetch_status -1)
BEGIN
–Print @name
–Print @owner_sid
/*
Select @sqltxt = ‘ USE ‘
Select @sqltxt = @sqltxt + @name + ‘ go’
Select @sqltxt = @sqltxt + ‘ sp_changedbowner sa’
print @sqltxt
execute @sqltxt
*/
set @exec_stmt = ‘alter authorization on database::’ + quotename(@name) + ‘ to ‘ + quotename(‘sa’ )
exec (@exec_stmt)
if @@error = 0
begin
Print ‘Owner of Database ‘ + @name + ‘ successfuly altered from owner NULL to sa login’
end
else
begin
RAISERROR (‘Owner of DB not altered’, 16, 1)
end
FETCH NEXT FROM c INTO @name,@owner_sid
END
CLOSE c
DEALLOCATE c
GO
Ed
here is one use to fix a job where owner = guy that left before it is null and fails or just fials due to invalid id as owner
set nocount on
use msdb
go
Declare @srvname varchar(55)
Select @srvname = @@servername
Print @srvname
DECLARE c CURSOR
READ_ONLY
FOR select name,owner_sid from msdb..sysjobs –where owner_sid 0x01
where SUSER_SNAME(owner_sid) = ‘DomainguyThatLeftId’
DECLARE @name varchar(255), @return int,@original_Owner varchar(60),@owner_sid varchar(50)
OPEN c
FETCH NEXT FROM c INTO @name,@owner_sid
WHILE (@@fetch_status -1)
BEGIN
exec msdb..sp_update_job @job_name = @name , @owner_login_name =’sa’
–select @original_Owner = name from master..syslogins where sid = @owner_sid
if @@error = 0
begin
Print ‘Owner of Job ‘ + @name + ‘ successfuly altered from orig owner to sa login’
end
else
begin
RAISERROR (‘Owner of Job not altered’, 16, 1)
end
FETCH NEXT FROM c INTO @name,@owner_sid
END
CLOSE c
DEALLOCATE c
GO
Ed
This will find owners and not make any changes:
Set nocount ON
Select Convert(varchar(25),@@servername) as ‘Server_Name Checked below:’
select
Convert(varchar(55),[name]) as ‘Job_Name’,
Convert(varchar(25),SUSER_SNAME(owner_sid)) AS Owner_name,
LEFT([date_created],11)AS Date_Created,
LEFT([date_modified],11)AS Date_Modified,
version_number
from msdb.dbo.sysjobs where SUSER_SNAME(owner_sid) is NULL
ORDER BY [Owner_name]
Print ”
Print ‘##################################################################’
Print ”
SQLSoldier
Thanks Ed. I have a script for that too. Do you have a blog that you post these to? If so, what is it?
Keith
What tool do you prefer to use to crack open the T-Log?
SQLSoldier
I don’t crack open the transaction log. I use the built-in functions fn_dblog, fn_dumpdblog, or DBCC Log.
Ed
I just kinda write them as I need them.
I use to lecture and post but got busy with an Autistic daughter for a fews years correcting that. Very nice slick scripts you posted above.
Ed
SQLSoldier
I know what you mean. I have an autistic nephew who lived with me until he was 8 years-old.
Something for the Weekend - SQL Server Links 31/05/13 • John Sansom
[…] Looking for Buried Treasure in the Transaction Log – How to inspect the transaction log, courtesy of Robert L. Davis(Blog|Twitter), to find out things such as who altered permissions on logins. […]
Day 312: Exceptions | The Year of Living Non-Judgmentally
[…] to James Thurber, SQL Soldier (for the buried treasure image), exception-makers, kind commitment-breakers, and to you, […]