Determine the Members of Each ServerRole

The VBScript extract below will determine the members of each SQL Server ServerRole for a list of servers provided in an input file.

Option Explicit


‘ File containing list of servers to check

Const SERVER_LIST = “c:\serverlist.txt”

‘ File to write the results to

Const OUTPUT = “c:\output.txt”

Const ForReading = 1

Const ForWriting = 2


Dim oFSO

Set oFSO = CreateObject(“Scripting.FileSystemObject”)


Dim oSQLServer

Set oSQLServer = CreateObject(“SQLDMO.SQLServer”)


oSQLServer.LoginSecure = True


Dim oFSORead

Set oFSORead = oFSO.OpenTextFile(SERVER_LIST, ForReading, False)


Dim oFSOWrite

Set oFSOWrite = oFSO.OpenTextFile(OUTPUT, ForWriting, True)


Dim i

Dim srvRole

Dim server

Dim results


‘ Loop through each server in the Serverlist file

Do While Not oFSORead.AtEndOfStream

server = oFSORead.ReadLine


oSQLServer.Connect server


‘ For each SQL Server ServerRole determine the members of each role

‘ and write the results to the Output file in a comma delimited format

‘ ie. servername, serverrole, login

For Each srvRole In oSQLServer.ServerRoles

Set results = srvRole.EnumServerRoleMember

For i = 1 To results.Rows

oFSOWrite.WriteLine server & “, ” & srvRole.Name & “, ” & results.GetColumnString(i, 1)









Set oFSO = Nothing

Set oSQLServer = Nothing