Extended Events vs Default Trace

  • Hi 
    I´m trying to get info when and by who a user account is created in SQL 2014( and later in SQL 2016) 
    I have a script that can do it from Default Trace.
    Is it possible to get the same info from Extended Events as from Default Trace ? 

    Regards
    Jonas

  • What you'd be looking at is the system_health Extended Events session. You can read up on what it does here. It doesn't monitor security changes. You can pretty easily set up a session that does though.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Hi
    I've created a session based on the event Object_created, but when i create an account nothing happends in Live View . It does when i create a database..
    Should I choose a different event ?

  • On further review, I was right and wrong at the same time. Extended Events does support capturing this information, but, it's through events that are not user configurable. You have to use the Audit functions. Server level audits are available for all versions. Database audits are only for Enterprise until SQL Server 2016 SP1 when they became available for all.

    Sorry for the confusion. You should be able to get the login creation from audit in 2014. You can get everything in 2016.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 4 posts - 1 through 3 (of 3 total)

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