Post reply

Show SQL authentication login information

  • May 12, 2011 at 2:03 am

    #250879

    Comments posted to this topic are about the item Show SQL authentication login information

    Rudy

  • May 12, 2011 at 5:40 am

    #1323592

    Is there a similar set of queries for Windows based logons?

  • May 12, 2011 at 6:46 am

    #1323618

    Twin-soft.com SME (5/12/2011)

    Is there a similar set of queries for Windows based logons?

    Windows authentication information is supplied by active directory. You should talk to your network administrators to get the same inforation.

    Now after saying that; I'm planning to create another script that will read this information right out of active directory from within SQL server. Only thing is that you will need Domain Admins rights to retrieve the infromation.

    I'll have it here as soon as it's done and tested.

    Thanks,

    Rudy

    Rudy

  • May 12, 2011 at 10:38 am

    #1323816

    Hey, you really inspired me today! We have some similar requirements for security auditing so I took the bones of your script to produce the scipt below. I did not want all your columns, but you could add them back in.

    This version doesn't need to loop it gathers everything in the select statement(s). I also removed the sa account, any certificate accounts, and domain users and groups.

    ----------------------------------------------------------

    -- SQL Login Audit --

    -- Find Local SQL Logins (remove sa, certificate users, --

    -- and nt authority) then audit the user --

    ----------------------------------------------------------

    Use Master

    GO

    select [name] as 'SQL User Name', [CreateDate] as 'CreateDate',(SELECT loginproperty([name], 'BadPasswordCount')) as 'Bad Password Count',(SELECT loginproperty([name], 'BadPasswordTime')) as 'Bad Password Time',

    (SELECT loginproperty([name], 'DaysUntilExpiration')) as 'Days Until Expiration',(SELECT loginproperty([name], 'DefaultDatabase')) as 'Default Database',

    (SELECT loginproperty([name], 'HistoryLength')) as 'History Length',(SELECT loginproperty([name], 'IsExpired')) as 'Is Expired',

    (SELECT loginproperty([name], 'IsLocked')) as 'Is Locked',(SELECT loginproperty([name], 'IsMustChange')) as 'Is Must Change',

    (SELECT loginproperty([name], 'LockoutTime')) as 'LockoutTime',(SELECT loginproperty([name], 'PasswordLastSetTime')) as 'PasswordLast Set Time'

    from syslogins

    where isntuser = '0' and isntgroup = '0'

    and [name] not in ('sa', '##MS_SQLResourceSigningCertificate##','##MS_SQLReplicationSigningCertificate##',

    '##MS_SQLAuthenticatorCertificate##', '##MS_PolicySigningCertificate##', '##MS_PolicyTsqlExecutionLogin##',

    'NT AUTHORITY\SYSTEM', '##MS_PolicyEventProcessingLogin##', '##MS_AgentSigningCertificate##')

  • May 12, 2011 at 11:15 am

    #1323841

    I'm glad this script inspired you today!

    Here's a question for you. Have you executed my script? It only reports on SQL authenticated user accounts. So you would not get any certificate users and nt authority accounts.

    As for "sa" I would leave that in the report as you would want to know if someone has been playing account with that account.

    Thanks,

    Rudy

    tim.shirey (5/12/2011)

    Hey, you really inspired me today! We have some similar requirements for security auditing so I took the bones of your script to produce the scipt below. I did not want all your columns, but you could add them back in.

    This version doesn't need to loop it gathers everything in the select statement(s). I also removed the sa account, any certificate accounts, and domain users and groups.

    ----------------------------------------------------------

    -- SQL Login Audit --

    -- Find Local SQL Logins (remove sa, certificate users, --

    -- and nt authority) then audit the user --

    ----------------------------------------------------------

    Rudy

  • May 12, 2016 at 7:45 am

    #1878646

    Thanks for the script.

  • May 12, 2016 at 10:48 am

    #1878728

    Iwas Bornready (5/12/2016)

    Thanks for the script.

    Wow, why not do something useful instead of just posting "Thanks for the script."

    Attached is a modified version of the script in the script library.

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

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