This is part 1 of a multipart posting on some PowerShell scripts that I’ve found and made some modifications to. These scripts I have used to document my environment without having to manually type information in to the spreadsheets. The master which links to all the scripts is here.

This script gathers Login information and membership in the built in roles. It also expects a flat file named Servers.txt with a list of SQL Servers to connect to. It will connect via SSPI, so make sure you run it with a Windows account with the appropriate permissions on each SQL Server.

#change these settings to your environment
$Filename='SQLLoginInformation'
#these are actually User ODBC DSNs DSNs. Better for Excel.

$serverList = "Servers.txt"

# constants.
$xlCenter=-4108
$xlTop=-4160
$xlUp=-4162
$xlSolid=1
$xlAutomatic=-4105
$xlOpenXMLWorkbook=[int]51
# and we put the queries in here
$SQL=@"
select 
	name AS [Login],
	dbname AS [Default Database],
	createdate AS [Create Date],
	updatedate  AS [Update Date],
	cast(hasaccess as tinyint) AS [Access],
	cast(denylogin as tinyint) AS [Deny Access],
	cast(isntname as tinyint) AS [Windows Name],
	cast(isntgroup as tinyint) AS [Windows Group],
	cast(isntuser as tinyint) AS [Windows User],
	cast(sysadmin as tinyint) AS [System Admin],
	cast(securityadmin as tinyint) AS [Security Admin],
	cast(serveradmin as tinyint) AS [Server Admin],
	cast(setupadmin as tinyint) AS [Setup Admin],
	cast(processadmin as tinyint) AS [Process Admin],
	cast(diskadmin as tinyint) AS [Disk Admin],
	cast(dbcreator as tinyint) AS [Database Creator],
	cast(bulkadmin as tinyint) AS [Bulk Admin], 
	totcpu AS [CPU Governed],
	totio AS [IO Governed],
	spacelimit AS [Space Governed],
	timelimit AS [Time Governed],
	resultlimit AS [Resultset Governed]
 from sys.syslogins
 order by name
"@


# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
#if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
#  {
#  New-Item "$DirectoryToSaveTo" -type directory | out-null
#  }
$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in (Get-Content $serverList)) 
	{ #only create the worksheet if necessary
	if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
	else  {$ws = $wb.Worksheets.Add()} #add if it doesn't exist
	$currentWorksheet += 1 
	$currentName=$server.Replace("\","_") # and name the worksheet
	$ws.name=$currentName # so it appears in the tab 
	# note we create the query so that the user can run it to refresh it
    
    $myconnectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=$server"  
    $adOpenStatic = 3 
    $adLockOptimistic = 3  
    
    $objConnection = New-Object -comobject ADODB.Connection 
    $objRecordset = New-Object -comobject ADODB.Recordset  
    
    $objRecordset.CursorLocation = 3
    
    $objConnection.Open($myconnectionstring) 
    $objRecordset.Open($SQL,$objConnection,$adOpenStatic,$adLockOptimistic)  
    
    $objRecordset.MoveFirst()
    
    $rows = $objRecordset.RecordCount+1
    
    $qt = $ws.QueryTables.Add($objRecordset,$ws.Range("A1"))
	# and execute it
	if ($qt.Refresh())#if the routine works OK
		{
	              $ws.Activate()
		$ws.Select()
		$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
		$excel.Rows.Item(1).VerticalAlignment = $xlTop
		$excel.Rows.Item(1).Orientation = -90
		$excel.Columns.Item("E:I").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
        $excel.Columns.Item("J:M").NumberFormat = "[Green][=0]û;[Red][=1]«"
        $excel.Columns.Item("N:Q").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
        # Color the columns below to highlight the superuser permissions
        $excel.Range("J2","M"+$rows).Interior.Pattern=$xlSolid
        $excel.Range("J2","M"+$rows).Interior.PatternColorIndex=$xlAutomatic
        $excel.Range("J2","M"+$rows).Interior.Color=12320247
        $excel.Range("J2","M"+$rows).Interior.TintAndShade = 0
      
		$excel.Columns.Item("E:Q").Font.Name = "Wingdings" 
		$excel.Columns.Item("E:Q").Font.Size = 12
		$excel.Rows.Item("1:1").Font.Name = "Calibri"
		$excel.Rows.Item("1:1").Font.Size = 9
		$excel.Rows.Item("1:1").Font.Bold = $true
		$excel.Columns.Item(1).Font.Bold = $true
        $Excel.Columns.Item("A:Q").Font.Size = 9
        [void] $Excel.Columns.AutoFit()
		}

	}
$filename=$filename -replace  '[\\\/\:\.]',' ' #remove characters that can cause problems
$filename = "$filename.xlsx" #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$objRecordset.Close() 
$objConnection.Close() 
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev's Excel trick 'Pro Windows Powershell' p380
[GC]::Collect()