PowerShell and SMO – be careful how you iterate
- by Fatherjack
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.
