I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.
I’ve been wandering through the dbatools set of cmdlets, trying to see where various cmdlets are useful, and also practicing some PoSh skills.
Recently I noticed there was a Get-DbaRoleMember cmdlet to use. Of course, when I tried to check it, I found an issue. I didn’t have the cmdlet in my system. Autocomplete didn’t find it and running the cmdlet returned an error. I assumed that there had been a dbatools update, and I hadn’t gotten it.
I run into this before, and a quick query to @sqlvariant helped me realize I needed to run update-module, not re-run import-module. I did that and it worked smoothly (I did need to be an administrator to update this).
One I did this, I could run the cmdlet and get data.
Interesting, but this doesn’t seem incredibly useful. After all, I can easily query this in T-SQL, and my monitoring software will check to see if roles change.
However, perhaps I am actually going to write something that checks to see if we have consistent sysadmins on all instances. Or perhaps we’re looking to add a sysadmin to an instance where he/she doesn’t exist. In any case, PoSh is a way to easily connect to multiple instances on many machines in a way that’s more cumbersome in T-SQL or SQLCMD.
I can get the server level roles with –IncludeServerLevel parameter. When I do that, I see my roles and members.
I can also connect to remote servers and get this:
How can I do this in bulk? Well, I can certainly run this multiple times with a list of instances. Let’s make a quick list. I’ll make a quick array:
Now I’ll use that in a foreach loop, sending the server name to the cmdlet and looking for sysadmins.
And I have the sysadmins on all of my instances. Certainly this isn’t terribly useful by itself, but I can easily add more programming that looks for a user, maybe checks for which machines have a sysadmin, maybe add (or remove) a sysadmin from instances that are out of sync with what I need.
This isn’t one of the cmdlets I’d use the most often, but I can see it being handy when trying to automate some of the permissions checks across multiple machines that I need to keep in sync. At the very least, this could be helpful in comparing permissions between primary and secondary (or DR) nodes.
Note: I haven’t tried it, but I suspect that the migration cmdlets at dbatools would be better for keeping primary and secondaries in sync.