Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Show SQL authentication login information


Show SQL authentication login information

Author
Message
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1302
Comments posted to this topic are about the item Show SQL authentication login information



Rick.Cornell.SME
Rick.Cornell.SME
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5305 Visits: 208
Is there a similar set of queries for Windows based logons?
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1302
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



tim.shirey
tim.shirey
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 151
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##')
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1302
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


[quote]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 --
----------------------------------------------------------



Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8620 Visits: 885
Thanks for the script.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24167 Visits: 37929
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Attachments
SQLLoginInfo.txt (3 views, 4.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search