Click here to monitor SSC

Jonathan has been working with SQL Server since 1999. He enjoys performance tuning, development and using SQL Server to provide appropriate business solutions. He is the founder and leader of the PASS SQL South West user group , is a moderator at SQL Q + A forum and is on twitter at @fatherjack. He has spoken at SQLBits and SQL in the City, SQL Saturdays and local user groups across the UK and Europe.

PowerShell and SMO – be careful how you iterate

Published 27 May 2014 10:51 am

I’ve yet to have a totally smooth experience with PowerShell and it was late on Friday when I crashed into this problem. I haven’t investigated if this is a generally well understood circumstance and if it is then I apologise for repeating everything.

Scenario: I wanted to scan a number of server for many properties, including existing logins and to identify which accounts are bestowed with sysadmin privileges. A great task to pass to PowerShell, so with a heavy heart I started up PowerShellISE and started typing. The script doesn’t come easily to me but I follow the logic of SMO and the properties and methods available with the language so it seemed something I should be able to master.

Version #1 of my script.


And the results it returns when executed against my home laptop server.


These results looked good and for a long time I was concerned with other parts of the script, for all intents and purposes quite happy that this was an accurate assessment of the server.

Let’s just review my logic for each step of the code at the top.

Lines 1 to 7 just set up our variables and write out the header message

Line 8 our first loop, to go through each login on the server

Line 10 an inner loop that will assess each role name that each login has been assigned

Line 11 a test to see if each role has the name ‘sysadmin’

Line 13 write out the login name with a bright format as it is a sysadmin login

Line 17 write out the login name with no formatting

It is quite possible that here someone with more PowerShell experience than me will be shouting at their screen pointing at the error I made but to me this made total sense.

Until I altered the code, I altered lines 6 and 7 of code above to be:

$c = $Svr.Logins.Count

write-host “There are $c Logins on the server”

This changed my output to look like this:


This started alarm bells ringing – there are clearly not 13 logins listed

So, let’s see where things are going wrong, edit the script so it looks like this. I’ve highlighted the changes to make


Running this code shows me these results


Our $n variable should count up by one for each login returned and We are clearly missing some logins.

I referenced this list back to Management Studio for my server and see the Logins as below, where there are clearly 13 logins.


We see a Login called Annette in SSMS but not in the script results so I opened that up and looked at its properties and it’s server roles in particular.


The account has only public access to the server. Inspection of the other logins that the PowerShell script misses out show they too are only members of the public role.

Right now I can’t work out whether there is a good reason for this and if it should be expected behaviour or not. Please spend a few minutes to leave a comment if you have an opinion or theory for this.

How to get the full list of logins. Clearly I needed to get a full list of the logins so set about reviewing my code to see if there was a better way to iterate through the roles for each login.

This is the code that I came up with and I think it is doing everything that I need it to.


It gives me the expected results like this:


So it seems that the ListMembers() method is the trouble maker in my first versions of the code. I would have expected that ListMembers should return Logins that are only members of the public role, certainly Technet makes no reference to it being left out in it’s Login.ListMembers details. Suffice to say, it’s a lesson learned and I will approach using it with caution in future circumstances.

4 Responses to “PowerShell and SMO – be careful how you iterate”

  1. Stuart Moore says:

    Hi Jonathan,
    Not actually a PowerShell issue as such. It’s to do with the way SQL Server implements the Public Role. It doesn’t implement the same way as any other role, it appears to just assume every user is a member rather than actually keeping track of who’s a member. It’s the same reason that

    sp_helpsrvrolemember ‘public’

    will also error.

    I’m sure I’ve read something about this in a book (one of Kalen Delaney’s I think). Best I can seem to find online is reading between the lines in these 2 pages:

    and SMO is just calling the underlying T-SQL functionality, so has the same limitations

    An alternative way of checking is like this:

    foreach ($login in $sqlsvr.logins){
    if ($login.listmembers() -contains 'sysadmin'){
    write-host $ -ForegroundColor yellow
    write-host $

    Then if you get an empty result back from listmembers() you’ll just drop through to the default output

    Hope that made sense.


  2. Fatherjack says:

    Hi Stuart, thanks for reading and adding your valuable comments.

    I can see that the issue isn’t with PowerShell per sé but in the SMO implementation but it seems a very easy trap for anyone using the method to fall into. Are there (m)any other places where this sort of thing happens?

    I like your method for checking the roles, is there any performance advantage with either?


  3. Stuart Moore says:

    I expect there are, but off hand I can’t think of any. I’ve certainly run into problems when DBs are offline or restoring and I’ve tried to loop through that collection.

    It’s one of the beauties of using contains rather than looping and eq that if an object/property is missing you can still catch it, whereas foreach will just skip it.

    I’m led to believe that there is, especially if you’re scanning larger collections as it’ll match the first occurrence and then return, the foreach and if construct will keep on running after the first match.


  4. Fatherjack says:

    OK, I’d spotted the continued evaluation so had been exiting the loop as soon as I got a match but using contains is the future.

    Thanks again!

Leave a Reply

Blog archive