Finding Sysadmins with dba tools–GetDbaRoleMember

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).

2017-01-20 09_09_12-cmd - powershell (Admin)

One I did this, I could run the cmdlet and get data.

2017-01-20 09_09_55-cmd - powershell (Admin)

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.

2017-01-20 09_51_34-Atlas Home Lab .201 - VMware Workstation

I can also connect to remote servers and get this:

2017-01-20 09_52_25-Atlas Home Lab .201 - VMware Workstation

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.

2017-01-20 09_56_36-Atlas Home Lab .201 - VMware Workstation

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

2 Responses to Finding Sysadmins with dba tools–GetDbaRoleMember

  1. Klaas Vandenberghe says:

    Hi Steve

    Thank you so much for using and reviewing dbatools.

    For clarity, let me say first I am the author of Get-DbaRoleMember.

    Here are some extra tips.

    I’d like to advise new users to look at the help included in our functions.
    The way I like the most is this:
    > help Get-DbaRoleMember -ShowWindow
    This way the documentation is available in a new window and it stays there while we can try the function out in our PowerShell Host.

    There’s one powerful technique you can use when passing in multiple Sql instances:
    there’s no need to use the foreach!
    Try this:
    > $instances | Get-DbaRoleMember
    > Get-DbaRoleMember -SqlInstance $instances

    The output of most of our functions is easily redirected to a .csv, HTML, an e-mail or a table.
    We will provide guidance on how to that in the near future.
    For now, maybe one handy example:
    > Get-DbaRoleMember -SqlInstance $instances | Out-GridView

    Thanks again, Steve. And to all DBA’s trying out dbatools, we appreciate all feedback.
    And if you’re looking forward to more useful functions, follow us on


    • way0utwest says:

      Thanks for the tips, Klaas, and the tools. I’m happy to review them and I’m enjoying seeing how useful they are. I’ll give these a try and write a followup.


Comments are closed.