The Multifunctioning DBA

May 26 2010   7:00AM GMT

Powershell to get unique login count from SQL Server

Colin Smith Colin Smith Profile: Colin Smith

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.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: