Instance level trigger to store login information (who created what)

  • Lord Slaagh

    SSCertifiable

    Points: 5603

    Hello SSC,

    I am looking for a simplified way to track which user created which login, Surprisingly enough, the sys.logins table does not have this information. My company has SQL environments ranging from 2008 R2 - 2017. So this is going to be challenging. At the moment, we have no way in knowing who created logins on our production environments.

    So, since our outdated environment goes back as far as 2008 R2, I figured instance level triggers would do the trick. I am not sure if SQL has that capability on 2008 R2, 2012, 2014, 2016, or 2017. I know there are DLL triggers, but are they instance level?

    Also, what would SSC recommend for this, if not triggers? Extended events is an option, but it's doubtful that my company will approve this method.

    Any help would be greatly appreciated!

    Everyone has a plan until they get punched in the mouth. --Mike Tyson

  • ScottPletcher

    SSC Guru

    Points: 98398

    It's not really an instance-level trigger, it's a db level trigger for the master db, because all logins are created in the master db.  The DDL event would be CREATE_LOGIN (and ALTER_LOGIN if you care about that).

    I'm not familiar with a "sys.logins" view, but I'm not surprised that SQL doesn't capture creator info, etc., since the overhead of doing so would be enormous and the vast majority of people would never need it or use it.

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Lord Slaagh

    SSCertifiable

    Points: 5603

    Thanks for the quick response, but my company will never allow a trigger in master.

    Do you know of any SQL 3rd party tools that handle something like this?

    Everyone has a plan until they get punched in the mouth. --Mike Tyson

  • Grant Fritchey

    SSC Guru

    Points: 396384

    Why go with triggers? Use Extended Events. I don't have a blog post on tracking creation of logins using Extended Events available. Here's one on auditing database changes. You can just do a quick search for object creation and auditing to find the right event. This will be much easier, with lower resource use, and doesn't involve doing weird stuff to the master database. Further, setting these up on multiple servers is fast & easy.

    ----------------------------------------------------
    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

  • Johan Bijnens

    SSC Guru

    Points: 134280

    have a look at Scope: The drastic caveat with Logon Triggers

     

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • ScottPletcher

    SSC Guru

    Points: 98398

    I tried to come up with something other than Extended Events because of this in OP's q:

    Extended events is an option, but it's doubtful that my company will approve this method.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717790

    I would lean towards XE myself, though I'm not sure why your company would not allow that.

    I believe SQL Audit will capture this, but I'd have to dig in. There is an event for logins.

    Audit - https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15

    Events - https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/audit-login-change-property-event-class?view=sql-server-ver15

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

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