How to know the which user can drop my table in azure database

  • polo.csit

    SSCrazy

    Points: 2560

    Hi All,

    Someone has dropped a table from azure database and i want to track who did it.

    How to know the which user can drop my table in azure database.

     

     

    Regadrs

    Polaiah M

    • This topic was modified 3 weeks, 2 days ago by  polo.csit.
  • Andrey

    SSChasing Mays

    Points: 628

    polo.csit wrote:

    Hi All,

    Someone has dropped a table from azure database and i want to track who did it.

    afaik, it's not possible out-of-the-box in Azure.

    polo.csit wrote:

    How to know the which user can drop my table in azure database.

    For example, users with either granted control to the appropriate db schema  or  members of db_owner group.

    It depends on security model implemented in your db.

     

  • polo.csit

    SSCrazy

    Points: 2560

    SELECT

    Operation,

    [Transaction Id],

    [Transaction SID],

    [Transaction Name],

    [Begin Time],

    [SPID],

    Description

    FROM fn_dblog (NULL, NULL)

    WHERE [Transaction Name] = 'DROPOBJ'

    is not available in Azure?

  • Eric M Russell

    SSC Guru

    Points: 125010

    The object drop events are logged in the default trace, and from there you can get date/time, session login name, etc.

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/default-trace-enabled-server-configuration-option

     

     

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Andrey

    SSChasing Mays

    Points: 628

    Eric M Russell wrote:

    The object drop events are logged in the default trace, and from there you can get date/time, session login name, etc.

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/default-trace-enabled-server-configuration-option

    Default trace is not available in Azure  SQL  db.

     

  • Andrey

    SSChasing Mays

    Points: 628

    polo.csit wrote:

    SELECT

    Operation,

    [Transaction Id],

    [Transaction SID],

    [Transaction Name],

    [Begin Time],

    [SPID],

    Description

    FROM fn_dblog (NULL, NULL)

    WHERE [Transaction Name] = 'DROPOBJ'

    is not available in Azure?

    It's an option, but when you have no control over backups (log backups, for instance), the chance to get the info is very little, depending on activity in your database.

     

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply