4

I was reading this blog post which contains a query that can allow you to find when a DROP was fired. I changed LIKE 'DROPOBJ%' to NOT LIKE 'DROPOBJ%' so I can return more rows:

SELECT [Current LSN]
        ,[Operation]
        ,[Context]
        ,[Transaction ID]
        ,[Description]
        ,[Begin Time]
        ,[Transaction SID]
        ,[Transaction Name]
FROM fn_dblog (NULL,NULL)
INNER JOIN(SELECT [Transaction ID] AS tid
FROM fn_dblog(NULL,NULL)
WHERE [Transaction Name] NOT LIKE 'DROPOBJ%')fd ON [Transaction ID] = fd.tid

The query executes easily on Azure SQL Database, but when I try to add the SUSER_SNAME as explained in the blog post to the code:

SELECT [Current LSN]
        ,[Operation]
        ,[Context]
        ,[Transaction ID]
        ,[Description]
        ,[Begin Time]
        ,[Transaction SID]
        ,SUSER_SNAME ([Transaction SID]) AS WhoDidIt
FROM fn_dblog (NULL,NULL)
INNER JOIN(SELECT [Transaction ID] AS tid
FROM fn_dblog(NULL,NULL)
WHERE [Transaction Name] NOT LIKE 'DROPOBJ%')fd ON [Transaction ID] = fd.tid

Azure SQL Database returns the error:

Msg 40507, Level 16, State 1, Line 1
'SUSER_SNAME' cannot be invoked with parameters in this version of SQL Server.

I thought that must be because SUSER_SNAME is not supported on Azure SQL Database but if I go to check the documentation it says:

SUSER_NAME always return the login name for the current security context. The SUSER_SNAME statement does not support execution using an impersonated security context through EXECUTE AS.

But here I'm not using EXECUTE AS and in fact if I run:

SELECT SUSER_SNAME();  
GO 

Everything is fine and the query returns my e-mail:

enter image description here

Where am I wrong here?

1 Answer 1

8

The SUSER_NAME documentation is incomplete. SUSER_NAME in Azure SQL Database doesn't support the server_user_sid argument. I submitted a pull request for the doc change.

Try joining to sys.database_principals instead of using a server-scoped function. This will get names of database-scoped principals.

SELECT 
         l.[Current LSN]
        ,l.[Operation]
        ,l.[Context]
        ,l.[Transaction ID]
        ,l.[Description]
        ,l.[Begin Time]
        ,l.[Transaction SID]
        ,dp.name AS WhoDidIt
FROM fn_dblog (NULL,NULL) l
INNER JOIN(SELECT [Transaction ID] AS tid
    FROM fn_dblog(NULL,NULL)
    WHERE [Transaction Name] NOT LIKE '%DROPOBJ%') AS fd ON l.[Transaction ID] = fd.tid
LEFT JOIN sys.database_principals AS dp ON dp.sid = l.[Transaction SID];

I see users dbo for schema changes by made by me (the admin) and other user names for changes made by other database users (created with CREATE USER UserName WITH PASSWORD=.... The value is NULL for non-database-scoped principals like system processes.

2
  • Thanks for the PR, Dan. We've had an internal discussion about this article, and refreshed it to follow our newer style guidelines. You can expect to see the updated article in the next day or so. Dec 21, 2022 at 18:06
  • Thanks, @RandolphWest.
    – Dan Guzman
    Dec 21, 2022 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.