ReshadIT (guest)
 |
| 03/27/2008 4:46 PM |
 Quote
 Reply
 Alert
|
Hi guys,
I have a auditing task and Chip recommended a SQLDump utility (Worked great! Thanks Chip!) The one thing I found out later which is required is to provide a report of how many of the logins are using windows authentication and how many of the logins on the same server is using sql authentication. I suppose you can view this just by looking at it but I am a LAZY DBA so I would like to know if there are any system tables I can get the information out of?
Any thoughts?
Thanks
ReshadIT@hotmail.com |
|
|
|
|
Chip Andrews
Posts:114
|
| 03/27/2008 8:12 PM |
Quote
Reply
Alert
|
Cheap and easy?
select isntname, count(*) as UserCount from syslogins group by isntname
Is that what you are looking for?
(0=sql auth, 1=windows auth)
Note that groups are counted as users. You can filter those out by looking at "isntgroup"
|
|
|
|
|
ReshadIT (guest)
|
| 03/28/2008 2:41 PM |
Quote
Reply
Alert
|
Chip,
This is exactly what I am looking for thank you very much. I did not know what the columns meant that is why I couldnt catch it at first. Can you please elaborate on what you mean by
"Note that groups are counted as users. You can filter those out by looking at "isntgroup""
I viewd the column on my table and all the values are zeros. Are you saying that If I only want the logins that I should exclude "isntgroup" ?
Thanks in advance, |
|
|
|
|
ReshadIT (guest)
|
| 03/28/2008 2:44 PM |
Quote
Reply
Alert
|
Thank you Chip! I did not know what those columns meant so I wasnt sure of the data I would get back. That is exactly what I needed.
Please explain what you meant by excluding the 'isntgroup' column if I wanted only the sql users? I just wanted the logins I guessing what you meant was if I wanted an accurate count to exclude the 'instgroup' with and <> statement? |
|
|
|
|
Chip Andrews
Posts:114
|
| 03/28/2008 4:18 PM |
Quote
Reply
Alert
|
Take a look at the query below and it should answer those questions:
select CASE isntname WHEN 0 THEN 'SQL Auth' ELSE 'NT Auth' END as AuthType, count(*) as UserCount
from syslogins
where isntgroup=0
group by isntname
In this case - we'll just exclude any NT Group access. Now - this does mean that you will not get full count of all potential users since we do not know the group memberships of those NT groups. However - if all you care about is the logins (direct user logins) then this will give you those counts.
|
|
|
|
|
ReshadIT (guest)
|
| 03/28/2008 7:18 PM |
Quote
Reply
Alert
|
| Thanks Chip! That answers my question. |
|
|
|
|