Often there is a requirement to enforce SQL Server Security Policies. This powershell script - in part 7 - will take a list of sql server instances, iterate through every instance , generate a list of Logins and return TRUE or FALSE for the fixed server roles:SYSADMIN,SECURITY,SERVER ADMIN,SETUP ADMIN,PROCESS ADMIN,DISK ADMIN,DBCREATOR,BULK ADMIN.

The powershell script will store the resultset into a Excel spreadsheet, and save the file . This is a useful tool for Audits , and typically I would run this script , and then chase sql server owners to clear up.

Let's break down the script

Part 1

defines the variables and importantly sets the names of : a)the instance list text file and output file the assumption is that all relevant files exist in the same path as the powershell scripts The instance.txt file is of the following format , include the column headers and as many instances as required:

Part 5 Instantiate SMO Create an SMO connection to the instanceThe $logins = $s.Logins represents a collection of Login objects. Each Login object represents a login defined on the instance of SQL Server.

Part 6iterate through the collection A TRUE or FALSE will return for every fixed server role requested. An then placed in the Excel spreadsheet