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)
Next
Next
oSQLServer.DisConnect
Loop
oFSORead.Close
Set oFSO = Nothing
Set oSQLServer = Nothing
