Posted by: Colin Smith
AD, Administration, DBA, Powershell, Reporting
Just like I needed to get a count of unique logins to an application on one of our Sybase instances I also needed to get these values for a few of our SQL Servers. Since SQL Server uses mixed mode authentication and we are using AD groups to aid in the security of the SQL Server, I could not just write a query against the instance to get a list of unique logins. I could have but it would have returned the groups and not the individuals that are members of the groups. So I wrote a PS Script to go out and connect to the SQL Server, get a list of logins and then parse the AD Groups to get me a listing of all the members of those groups. Then it takes that list and gets unique logins, since a user may be a member of more than one group, and then counted the number of users. First I made a connection to the SQL Server using SMO, then I find all the logins and filter them based on whether they are a user or a group. If it is a group then I query the domain to find the group members and output all those users as well as the local sql users to a file. Once I have looped thru all groups then I get the contents of the file and sort and get unique and then I select a count of the new object. That is it. If you have any questions or would like to see the code then just let me know.