LOGINS - Who Created it and When

Question

Post reply

LOGINS - Who Created it and When

YSLGuru

SSC-Insane

Points: 21661
More actions
May 18, 2010 at 12:49 pm

#386956

I'm hoping this is n;t a dumb question but is there no way in SQL Server to find out WHO created a Login? I know I can find out when a Login was created be it a SQL Login or one from a Domain acct but darned if I can see how you tell whom created that account.
I would think this would be a big security red flag and so I'm assuming I am just not looking in the right place.
Is there no way to determine this kind of security info?
Thanks
Kindest Regards,
Just say No to Facebook!

Viewing 11 posts - 1 through 10 (of 10 total)

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

The only way I know is to have a DDL trigger enabled for CREATE_LOGIN.

Greg

Lowell SSC Guru Points: 323518 More actions · Answer 2

looks like you'll need to add a trace specifically for login events.

it's not part of the default trace, i both tested it and checked the default trace's definition...no login audits at all, just DDL stuff.

There is a nice article here on adding a login trace, and scheduling it too:

http://www.sqlservercentral.com/articles/Administration/trace/872/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

I tried to do this a while ago and had no luch finding a way. sysuser does not contain such information and I found no table anywhere that would hold such information. the best information I got was that if a user was created recently enough then you could search the default trace and see the created user event.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

YSLGuru SSC-Insane Points: 21661 More actions · Answer 4

[Imagine well drawn image of human face with mouth gaping wide open and a look of shock and horror on the face]

IN order to create a Login you must connect as a user with CREATE LOGIN permissions so the SQL Server instance knows who it is or at least the account being used to create the Login and so it makes no sense to me why at least some piece of thsi would not be automatically stored along with the date & time the acct was created.

Even with the initial setup, a brand new SQL Server install you have to connect as either the sa or a domain admin to create that first SQL Login or to add that Domain Acct.

Am I the only one who finds this a little odd that by version 2008 (R2) of SQL Server that there does not appear to be any default way of tracking this?

Perhaps I'm making too big a deal out of security? Up until now we (at my comapny) have not had a SQL Server instance that anyone besides mysefl had CREATE LOGIN capabailities on and so this issue never came up. Now I have a box with a dozen or so logins and of courtse no one has said jack about having created any of these. The folks on teh domain with teh ability to do this aren't security risks but I don't like seeing new Logins or accts or anything else on the server thats not listed in our tracking log (where we record any additions.changes made to anything).

Kindest Regards,

Just say No to Facebook!

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Am I the only one who finds this a little odd that by version 2008 (R2) of SQL Server that there does not appear to be any default way of tracking this?

Auditing for that is an option of SQL Server Audit in SQL 2008 Enterprise. It would be nice if it was included in Standard edition, but until it is, we're stuck using one of the methods Lowell and I mentioned.

Greg

Lowell SSC Guru Points: 323518 More actions · Answer 6

some powerful thread pruning occurred;

i had posted the following and then reported them to the SSC gods:

i'm thinking that chinnu.saha and pinky.bd420 are bots that are just designed to post their links to infinity parts;
their saved answers make sense only sometimes...i bet if we google the answers, they were lifted from other posts.

i went to edit my post to say, yep hear's proof of where the text for piny's post came from:

http://custom.simplemachines.org/mods/index.php?mod=1364

and my post, the bot posts, and someone else who said "HUH?" were all gone.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 7

Lowell (5/19/2010)
some powerful thread pruning occurred;
i had posted the following and then reported them to the SSC gods:
i'm thinking that chinnu.saha and pinky.bd420 are bots that are just designed to post their links to infinity parts;
their saved answers make sense only sometimes...i bet if we google the answers, they were lifted from other posts.
i went to edit my post to say, yep hear's proof of where the text for piny's post came from:
http://custom.simplemachines.org/mods/index.php?mod=1364
and my post, the bot posts, and someone else who said "HUH?" were all gone.

Apolagies for asking a dumb question but what does the above mean? I am just nmow checking this since replying yesterday so perhaps some posts that were here before that are now gone would make this make sense?

Kindest Regards,

Just say No to Facebook!

Dan.Humphries SSChampion Points: 12125 More actions · Answer 8

there was a post from a user with the following text and I responded with basically HUH?

"I give no guarantee that your users will actually read and comprehend the information you direct them to. In fact, I can't even guarantee that they'll look at the topic. The word 'read' in my mod refers to SMF's definition, that is, to view the page. Apart from that, this mod cannot and will not do anything further to ensure that your users actually know anything about what you redirect them to.

"

lowell explained that the post likely came from a bot

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Dan.Humphries SSChampion Points: 12125 More actions · Answer 9

Note the text I posted is directly from the link the lowell posted.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

YSLGuru SSC-Insane Points: 21661 More actions · Answer 10

Dan.Humphries (5/19/2010)
Note the text I posted is directly from the link the lowell posted.

Dan,

Gotcha, thanks for explaining that.

BTW I did get a reply on this at the Microsoft forum for SQL Server 2008, from a MVP, and interestingly enough there is no out of the box or built in way to get this info as of 2008 R2. There are mechanisms that allow you to setup processes to get this after the fact but that is of little help to someone coming into an existing system who needs to identify what is already in place. When I get a SQL Server instance from our Network/IT people and it either already has a bunch of logins on it (which aren't clearly connected to the IT people as would be the case if I saw logins whcih were their 'firstName.LatName') or develops several before I get a chnace to audit it and setup my standard DBA objects whcih are on every sever (common logins, service configuration, etc) .

I work in an accounting environment and it just shocks me that this kind of audting is not built in and in such a way that you can not create a security princicpal without the system identifying and storing who you are along with other meta data about what you created.

Thanks again

Kindest Regards,

Just say No to Facebook!