Powershell to document SQL Logins

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
#these are actually User ODBC DSNs DSNs. Better for Excel.

$serverList = "Servers.txt"

# constants.
# and we put the queries in here
	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
    $rows = $objRecordset.RecordCount+1
    $qt = $ws.QueryTables.Add($objRecordset,$ws.Range("A1"))
	# and execute it
	if ($qt.Refresh())#if the routine works OK
		$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.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
$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

3 thoughts on “Powershell to document SQL Logins

  1. Looks like it worked great except the excel file save

    Exception calling “SaveAs” with “2” argument(s): “SaveAs method of Workbook class failed”
    At C:\Duane\POWERSHELL\sec_audit_socks_compliance_GET2.ps1:111 char:11
    + $wb.SaveAs <<<< ($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

    • Duane, thanks for the update. Sorry the “SaveAs” isn’t working for you. Do you have a file named that already in the same directory? That’s one error I’ve experienced with this script. The other is if you don’t have permissions to the directory you’re saving it to.

      I like your script, I’ve taken a different approach on a new script that I’m almost finished with. It uses SMO to get all the logins, all the permissions on the server and all the users and permissions on the databases, and generates the SQL to recreate all of it… either on the same server, or on a different server. I should be posting it soon. It’s less of an audit script, and more of a DR script.

  2. Really impressive output after I commented out the close excel file. I have a better query to use and I challenge you to get it to work. It has one result set per database on the server and the code in its current form only puts the first result set into a spread sheet.

    exec sp_MSforeachdb ‘
    use [?]
    @@SERVERNAME as ServerName
    ,DB_NAME() as dbname
    ,[Role] = roleprinc.[name]
    ,[DatabaseUserName] = memberprinc.[name]
    ,[UserType] = memberprinc.[type_desc]
    ,[PermissionType] = perm.[permission_name]
    ,[PermissionState] = perm.[state_desc]
    ,[ObjectType] = obj.type_desc
    ,[ObjectName] = OBJECT_NAME(perm.major_id)
    FROM sys.database_role_members as members
    JOIN sys.database_principals as roleprinc
    ON roleprinc.[principal_id] = members.[role_principal_id]
    JOIN sys.database_principals as memberprinc
    ON memberprinc.[principal_id] = members.[member_principal_id]
    LEFT JOIN sys.database_permissions as perm
    ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]

Leave a Reply

Your email address will not be published. Required fields are marked *