Post reply

Powershell script to list all sysadmins on many SQL Servers

  • October 29, 2019 at 12:59 pm

    #3692732

    Looking to satisfy requirement to iteratively connect to multiple SQL Servers in my environment (I have over 100) and render a list of SQL sysadmins on each SQL Server. (1st command below)

    If an A/D Group is returned in the list of sysadmins, I need to expand that A/D Group to list its members. (2nd command below)

    The 2 commands below work beautifully to manually perform this on a single SQL Server.  Not being a Powershell guru, wondering if someone has a PS script to perform this task.  (NOTE: I'm local admin and SQL sysadmin on ALL our SQL Servers)

    EXEC sp_helpsrvrolemember 'sysadmin'

    GO

    EXEC xp_logininfo 'MyDomain\My_AD_groupName', 'members';

    GO

    thx in advance for any help here!

    BT

  • October 29, 2019 at 7:22 pm

    #3692879

    I think what you are looking for is something similar to this website:

    https://key2consulting.com/how-to-execute-and-store-transact-sql-t-sql-queries-results-in-a-powershell-array/

    Basically, you create the objects then throw a query at it.  After getting the result, store it somewhere then repeat on the next server in your list.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • November 11, 2019 at 4:27 pm

    #3696780

    To satisfy the first need, you can use Get-DbaServerRoleMember to fetch all members of the sysadmin role.

    $ListOfInstances = @("Instance1","Server2\Instance2");
    $Sysadmins = Get-DbaServerRoleMember -SqlInstance $ListOfInstances -ServerRole sysadmin | Select-object SqlInstance, Name;

    From there, I would suggest using the AD module/cmdlets to interrogate AD for each name that's returned and find out if it's a user or group and if it's a group, use Get-ADGroupMember -recursive (to pick up nested groups) to get the usernames.

    • This reply was modified 5 years ago by  alevyinroc.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply