February 25, 2020 at 8:26 pm
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
February 25, 2020 at 9:06 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 25, 2020 at 9:41 pm
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
February 26, 2020 at 12:09 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2020 at 1:21 pm
have a look at Scope: The drastic caveat with Logon Triggers
Johan
Learn to play, play to learn !
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 but most of the time this is me
February 26, 2020 at 5:10 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 26, 2020 at 5:58 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply