SQL server security review using PowerShell

PROBLEM

How often do we do Security Reviews for your SQL Server Instances? We aim to stay on top of security and to be able to so we need to regularly review your Instance security configurations. It’s helpful to build a checklist to be used as part of an internal security audit review, and follow that checklist every time you conduct an audit. I don’t know about you, but the latter sounds boring to me. What would be awesome is to take that checklist and automate the whole SQL Server Security Review.

SOLUTION

The PowerShell Script below will evaluate your Instance level, Database Level Access and SQL Server Security options, and generate a HTML Report for you to check for any potential threats.

The Script already has help associated with it so you can download and simply use help to figure out how to use the script or alternatively you can download it to a PS-Scripts folder and provide parameters as shown below,

PS C:\PS-Scripts>.\Get-SQLSecurityReview -computer ServerName -instance ServerName\SQLInstance -report C:\temp\ServerName$SQLInstance-Security_Review.html

Verified on following platforms

Powershell v2.0 and higher versions

Microsoft SQL Server 2008 and higher versions

Windows Server 2008 and higher versions

You can download the script from the below link

SQL Server Security Review

ACTUAL SCRIPT

[CmdletBinding()] Param( [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)] [Alias('hostname')] [string]$computer, [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)] [string]$instance, [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)] [string]$report ) Function Get-SQLServerInfo { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" exec sp_configure 'show advanced options', 1; RECONFIGURE with override; ------------------------------------------------ SET NOCOUNT ON create table #spconfig(s_name varchar(50), mini bigint, maxi bigint, config_value bigint, run_value bigint) insert into #spconfig(s_name, mini, maxi,config_value, run_value) execute sp_configure declare @testvaluec as int declare @testvaluer as int set @testvaluec = (select config_value from #spconfig where s_name like '%xp_cmdshell%' ) set @testvaluer = (select run_value from #spconfig where s_name like '%xp_cmdshell%' ) --------------------------------------------------------------------------------------------------------------- ---------------------------------------------CONDITION 1 IF (@testvaluec =0 AND @testvaluer = 0) ----------------------------------------------BLOCK A BEGIN --------------------------------------------------- exec sp_configure 'show advanced options', 1; RECONFIGURE with override; exec sp_configure 'xp_cmdshell', 1; RECONFIGURE with override; --------------------------------------------------------------- declare @rootdir nvarchar(1000) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @rootdir OUTPUT IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath') BEGIN DROP DATABASE zzTempDBForDefaultPath END; CREATE DATABASE zzTempDBForDefaultPath; DECLARE @Default_Data_Path1 VARCHAR(512), @Default_Log_Path2 VARCHAR(512); SELECT @Default_Data_Path1 = ( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1) FROM sys.master_files mf INNER JOIN sys.[databases] d ON mf.[database_id] = d.[database_id] WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 0); SELECT @Default_Log_Path2 = ( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1) FROM sys.master_files mf INNER JOIN sys.[databases] d ON mf.[database_id] = d.[database_id] WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 1); IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath') BEGIN DROP DATABASE zzTempDBForDefaultPath END declare @ErrLogPath1 nvarchar(500) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg1', @ErrLogPath1 OUTPUT DECLARE @DBEngineLogin VARCHAR(100) EXECUTE master.dbo.xp_instance_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer', @value_name = N'ObjectName', @value = @DBEngineLogin OUTPUT DECLARE @tmpNewValue TABLE (newvalue varchar(500)) INSERT INTO @tmpNewValue EXEC xp_cmdshell 'systeminfo | findstr /c:"System Manufacturer"' DECLARE @localVariable varchar(500) SET @localVariable = (SELECT top 1 rtrim(ltrim(newvalue)) FROM @tmpNewValue ) --case when @localVariable like '%VMware%' OR @localVariable like '%hyper%'Then 'Virtual' else 'Physical' end as 'ServerType' select Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else 'STANDALONE' end as 'ServerType', case when @localVariable like '%VMware%' OR @localVariable like '%hyper%'Then 'Virtual' else 'Physical' end as 'ServerType(PhysicalorVirtual)', SERVERPROPERTY('MachineName') as 'MachineName', (SELECT TOP(1) c.local_net_address FROM sys.dm_exec_connections AS c WHERE c.local_net_address IS NOT NULL) as IPAddress,'MSSQL' as 'Technology', @@servername as 'InstanceName', (SELECT top 1 local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL and net_transport = 'TCP' and protocol_type not like 'Database Mirroring' and endpoint_id = 4) as 'port', @DBEngineLogin as 'ServiceAccount', left(@@VERSION, CHARINDEX(' - ',@@version)-1) as 'Version', SERVERPROPERTY ('Edition') as 'Edition', SERVERPROPERTY('productversion') 'VersionNumber', SERVERPROPERTY ('productlevel') as 'ServicePack', (select stuff((SELECT ', '+ cast (ROW_NUMBER() Over(order by dbid) as varchar)+'.'+ UPPER(name) FROM sys.sysdatabases FOR XML PATH ('')), 1, 1, '') as 'DB') as 'Databases', @Default_Data_Path1 as 'data', @Default_Log_Path2 as 'log', (select top 1 filename from sys.sysaltfiles where name like '%tempdev%' and filename like '%.mdf') as 'tempdbdata', (select top 1 filename from sys.sysaltfiles where name like '%templog%' and filename like '%.ldf') as 'tempdblog', @rootdir as 'root', SUBSTRING(substring(@ErrLogPath1, 1, len(@ErrLogPath1) - charindex('\', reverse(@ErrLogPath1))),3,500) as 'ErrorLogPath' --------------------------------------------------------------- exec sp_configure 'show advanced options', 1; RECONFIGURE with override; exec sp_configure 'xp_cmdshell', 0; RECONFIGURE with override; exec sp_configure 'show advanced options', 0; Reconfigure with override; ---------------------------------------------------------------- Drop Table #spconfig END ---------------------------------------------------------------- ---------------------------------------------CONDITION 2 ELSE IF (@testvaluec =1 AND @testvaluer = 1) ----------------------------------------------------------BLOCK B BEGIN ------------------------------------------------------------------- declare @rootdir3 nvarchar(500) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @rootdir3 OUTPUT IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath') BEGIN DROP DATABASE zzTempDBForDefaultPath END; CREATE DATABASE zzTempDBForDefaultPath; DECLARE @Default_Data_Path3 VARCHAR(512), @Default_Log_Path4 VARCHAR(512); SELECT @Default_Data_Path3 = ( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1) FROM sys.master_files mf INNER JOIN sys.[databases] d ON mf.[database_id] = d.[database_id] WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 0); SELECT @Default_Log_Path4 = ( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1) FROM sys.master_files mf INNER JOIN sys.[databases] d ON mf.[database_id] = d.[database_id] WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 1); IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath') BEGIN DROP DATABASE zzTempDBForDefaultPath END DECLARE @DBEngineLogin1 VARCHAR(100) EXECUTE master.dbo.xp_instance_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer', @value_name = N'ObjectName', @value = @DBEngineLogin1 OUTPUT declare @ErrLogPath nvarchar(500) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg1', @ErrLogPath OUTPUT DECLARE @tmpNewValue1 TABLE (newvalue varchar(500)) INSERT INTO @tmpNewValue1 EXEC xp_cmdshell 'systeminfo | findstr /c:"System Manufacturer"' DECLARE @localVariable1 varchar(500) SET @localVariable1 = (SELECT top 1 rtrim(ltrim(newvalue)) FROM @tmpNewValue1 ) --case when @localVariable like '%VMware%' OR @localVariable like '%hyper%'Then 'Virtual' else 'Physical' end as 'ServerType' select Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else 'STANDALONE' end as 'ServerType', case when @localVariable like '%VMware%' OR @localVariable like '%hyper%'Then 'Virtual' else 'Physical' end as 'ServerType(PhysicalorVirtual)', SERVERPROPERTY('MachineName') as 'MachineName', (SELECT TOP(1) c.local_net_address FROM sys.dm_exec_connections AS c WHERE c.local_net_address IS NOT NULL) as IPAddress,'MSSQL' as 'Technology', @@servername as 'InstanceName', (SELECT top 1 local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL and net_transport = 'TCP' and protocol_type not like 'Database Mirroring' and endpoint_id = 4) as 'port', @DBEngineLogin1 as 'ServiceAccount', left(@@VERSION, CHARINDEX(' - ',@@version)-1) as 'Version', SERVERPROPERTY ('Edition') as 'Edition', SERVERPROPERTY('productversion') 'VersionNumber', SERVERPROPERTY ('productlevel') as 'ServicePack', (select stuff((SELECT ', '+ cast (ROW_NUMBER() Over(order by dbid) as varchar)+'.'+ UPPER(name) FROM sys.sysdatabases FOR XML PATH ('')), 1, 1, '') as 'DB') as 'Databases', @Default_Data_Path3 as 'data', @Default_Log_Path4 as 'log', (select top 1 filename from sys.sysaltfiles where name like '%tempdev%' and filename like '%.mdf') as 'tempdbdata', (select top 1 filename from sys.sysaltfiles where name like '%templog%' and filename like '%.ldf') as 'tempdblog', @rootdir3 as 'root', SUBSTRING(substring(@ErrLogPath, 1, len(@ErrLogPath) - charindex('\', reverse(@ErrLogPath))),3,500) as 'ErrorLogPath' ------------------------------------------------------------------- exec sp_configure 'show advanced options', 0; Reconfigure with override; --------------------------------------------------------- drop table #spconfig END ---------------------------------------------------------- "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=300;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.CommandTimeout = 0; $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 300s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Function Get-DBOwners { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" select d.name [Database], d.owner_sid [Owner SID], p.name [Owner], p.type_desc [Owner Type] from sys.databases d left outer join sys.server_principals p on (d.owner_sid = p.sid) where d.database_id > 4 order by 1 "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=60;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 60s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Function Get-WindowAuthLogins { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" select name [Login], type_desc [Type], case when is_disabled = 0 then 'N' else 'Y' end as [Disabled?], create_date [Create Date], default_database_name [Default Database] from sys.server_principals where type in ('U', 'G') "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=60;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 60s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Function Get-SQLAuthLogins { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" select name [Login], type_desc [Type], case when is_disabled = 0 then 'N' else 'Y' end as [Disabled?], create_date [Create Date], default_database_name [Default Database], case when is_policy_checked = 0 then 'N' else 'Y' end as [Enforce Password Policy], case when is_expiration_checked = 0 then 'N' else 'Y' end as [Enforce Password Expiration] from sys.sql_logins order by 1 "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=30;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 30s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Function Get-ServerLevelPermissions { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" select pa.name [Grantee Login], pa.type_desc [Type], pb.name [Grantor Login], permission_name [Permission], state_desc [State] from sys.server_permissions s inner join sys.server_principals pa on (s.grantee_principal_id = pa.principal_id) inner join sys.server_principals pb on (s.grantor_principal_id = pb.principal_id) where pa.type in ('S', 'U', 'G', 'R') order by 1,4 "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=30;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 30s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Function Get-ServerRoleMembers { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" select pa.name [Role], pb.name [Login] from sys.server_role_members r inner join sys.server_principals pa on (r.role_principal_id = pa.principal_id) inner join sys.server_principals pb on (r.member_principal_id = pb.principal_id) order by 1,2 "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=60;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 60s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Function Get-DBLevelPermissions { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable')) drop table #tmpTable create table #tmpTable ( [Database] sysname NOT NULL, [Grantee Login] sysname NOT NULL, [Type] sysname NOT NULL, [Grantor Login] sysname NOT NULL, [Permission] sysname NOT NULL, [Applies To] sysname NOT NULL, [State] sysname NOT NULL) exec sp_msforeachdb 'use [?]; insert into #tmpTable select distinct db_name() [Database], pa.name [Grantee Login], pa.type_desc [Type], pb.name [Grantor Login], permission_name [Permission], class_desc [Applies To], state_desc [State] from sys.database_permissions d inner join sys.database_principals pa on (d.grantee_principal_id = pa.principal_id) inner join sys.database_principals pb on (d.grantor_principal_id = pb.principal_id) where pa.type in (''S'', ''U'', ''G'', ''A'', ''R'')' select * from #tmpTable order by 1,2,5,6 drop table #tmpTable "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=60;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 60s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Function Get-DBRoleMembers { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable')) drop table #tmpTable create table #tmpTable ( [Database] sysname NOT NULL, [Role] sysname NOT NULL, [Login] sysname NOT NULL) exec sp_msforeachdb 'use [?]; insert into #tmpTable select db_name() [Database], pa.name [Role], pb.name [Login] from sys.database_role_members r inner join sys.database_principals pa on (r.role_principal_id = pa.principal_id) inner join sys.database_principals pb on (r.member_principal_id = pb.principal_id)' select [Database], Role, Login from #tmpTable order by 1,2,3 drop table #tmpTable "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=30;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 30s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Function Get-SQLJobOwner { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" select j.name [Job], p.name [Owner], case when p.type like 'S' then 'SQL Login' when p.type like 'U' then 'Windows Login' end as 'Login Type' from msdb..sysjobs j inner join sys.server_principals p on (j.owner_sid = p.sid) "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=30;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 30s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Function Get-SQLServiceAccount { [CmdletBinding()] Param( [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)] [Alias('hostname')] [string[]]$ComputerName ) BEGIN{} PROCESS{ if (Test-Connection $Computer -Quiet -Count 2) { return Get-WmiObject -Class Win32_Service | Select Caption, startname, StartMode, Started | Where-Object {$_.Caption -like '*SQL*'} } else { Write-Host "Could not connect to $Computer." -ForegroundColor "Red" } } END{} } Function Get-SQLNetworkingProtocols { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('hostname')] [string]$ComputerName , [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ if (Get-Module -ListAvailable | ?{$_.Name -eq 'SQLPS'}){Push-location; Import-Module SQLPS -DisableNameChecking -WarningAction SilentlyContinue; Pop-Location;} else {Add-PSSnapin SQL* -WarningAction SilentlyContinue -ErrorAction SilentlyContinue;} try {add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop} catch {add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement"} $wmi = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" if ($sqlinstance -like '*\*') {$sqlinstance = $sqlinstance.Split("\")[1].Split(' ')} else {$sqlinstance = 'MSSQLSERVER'} $ComputerName = $ComputerName.ToUpper(); try{ $uri = "ManagedComputer[@Name='" + $ComputerName + "']/ ServerInstance[@Name='" + $sqlinstance + "']/ServerProtocol[@Name='Tcp']" $Tcp = $wmi.GetSmoObject($uri) $uri = "ManagedComputer[@Name='" + $ComputerName + "']/ ServerInstance[@Name='" + $sqlinstance + "']/ServerProtocol[@Name='np']" $np = $wmi.GetSmoObject($uri) $uri = "ManagedComputer[@Name='" + $ComputerName + "']/ ServerInstance[@Name='" + $sqlinstance + "']/ServerProtocol[@Name='sm']" $sm = $wmi.GetSmoObject($uri) try{ $uri = "ManagedComputer[@Name='" + $ComputerName + "']/ ServerInstance[@Name='" + $sqlinstance + "']/ServerProtocol[@Name='via']" $via = $wmi.GetSmoObject($uri) } catch{ Write-Host "VIA Protocol is discontinued" } } catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 30s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } PROCESS{ $sm | select @{n="Protocol Name";e={if($_.Name -like 'sm'){"Shared Memory"}}}, IsEnabled $np | select @{n="Protocol Name";e={if($_.Name -like 'np'){"Named Pipes"}}}, IsEnabled $Tcp | select @{n="Protocol Name";e={if($_.Name -like 'Tcp'){"TCP\IP"}}}, IsEnabled $via | select @{n="Protocol Name";e={if($_.Name -like 'via'){"Via"}}}, IsEnabled } END{} } Function Get-SQLTCPPort { [CmdletBinding()] Param( [string]$ComputerName, [string]$sqlinstance ) BEGIN{ if (Get-Module -ListAvailable | ?{$_.Name -eq 'SQLPS'}){Push-location; Import-Module SQLPS -DisableNameChecking -WarningAction SilentlyContinue; Pop-location;} else {Add-PSSnapin SQL* -WarningAction SilentlyContinue -ErrorAction SilentlyContinue;} try {add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop} catch {add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement"} $wmi = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" if ($sqlinstance -like '*\*') {$sqlinstance = $sqlinstance.Split("\")[1].Split(' ')} else {$sqlinstance = 'MSSQLSERVER'} Try { $ComputerName = $ComputerName.ToUpper(); $uri = "ManagedComputer[@Name='$ComputerName']/ ServerInstance[@Name='$sqlinstance']/ServerProtocol[@Name='Tcp']" $Tcp = $wmi.GetSmoObject($uri); } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 30s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } PROCESS{ return $wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties; } END{} } Function Get-LoginAuditing { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" declare @AuditLevel int exec master..xp_instance_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', @value_name='AuditLevel', @value=@AuditLevel output select case when @AuditLevel = 0 then 'None' when @AuditLevel = 1 then 'Successful Logins Only' when @AuditLevel = 2 then 'Failed Logins Only' when @AuditLevel = 3 then 'Successful Logins' end as 'LoginAuditing' "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=30;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 30s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Function Get-SQLGlobalSettings { [CmdletBinding()] Param( [Parameter(Mandatory=$true)] [Alias('instance')] [string]$sqlinstance ) BEGIN{ Try{ $query = @" exec sp_configure 'show advanced options', 1; RECONFIGURE with override; SET NOCOUNT ON create table #spconfig(s_name varchar(50), mini bigint, maxi bigint, config_value bigint, run_value bigint) insert into #spconfig(s_name, mini, maxi,config_value, run_value) execute sp_configure select s_name, case when run_value = 1 then 'Enabled' when run_value = 0 then 'Disbaled' End as 'CurrentSetting' from #spconfig where s_name in ('xp_cmdshell','SMO and DMO XPs','remote access','default trace enabled') drop table #spconfig exec sp_configure 'show advanced options', 0; RECONFIGURE with override; "@ $ErrorActionPreference = "Stop" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = $sqlinstance; Initial Catalog = Master; Integrated Security=true; Connection Timeout=30;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $ErrorActionPreference = "Continue" } Catch{ Write-Host "Unable to connect to the SQL Instance [$sqlinstance] in specified time-out period of 30s. Please Check the Instance availability and try again." -ForegroundColor "Red" } } Process{ return $DataSet.Tables[0] } END{ $SqlConnection.Close() } } Write-Host "[$(Get-date -DisplayHint date -Format g)]Script Execution Started ....`r`n" Write-Host "Collecting SQL Server Instance details ....`r`n" $serverinfo = Get-SQLServerInfo -sqlinstance "$instance" Write-Host "Checking Database Owners ....`r`n" $dbowners = Get-DBOwners -sqlinstance "$instance" Write-Host "Collecting details for Logins using Windows Authentication ....`r`n" $winauth = Get-WindowAuthLogins -sqlinstance "$instance" Write-Host "Collecting details for Logins using SQL Server Authentication ....`r`n" $sqlauth = Get-SQLAuthLogins -sqlinstance "$instance" Write-Host "Collecting Server Level Permissions ....`r`n" $serlvlperm = Get-ServerLevelPermissions -sqlinstance "$instance" Write-Host "Collecting Server Roles assigned to Logins ....`r`n" $serrolmem = Get-ServerRoleMembers -sqlinstance "$instance" Write-Host "Collecting Database Level Permissions ....`r`n" $dblvlperm = Get-DBLevelPermissions -sqlinstance "$instance" Write-Host "Collecting Database Roles Assigned to Logins ....`r`n" $dbrolmem = Get-DBRoleMembers -sqlinstance "$instance" Write-Host "Collecting Job Owner Details ....`r`n" $jobowners = Get-SQLJobOwner -sqlinstance "$instance" Write-Host "Checking SQL Server Services Logon Accounts ....`r`n" $servaccount = Get-SQLServiceAccount -ComputerName "$computer" Write-Host "Collecting Enabled Networking Protocols for SQL Server ...." $sqlprotocols = Get-SQLNetworkingProtocols -computer "$computer" -sqlinstance "$instance" Write-Host "`r`nCollecting SQL Server Port Details ....`r`n" $tcpports = Get-SQLTCPPort -computer "$computer" -sqlinstance "$instance" Write-Host "Checking if Login Auditing is Enabled ....`r`n" $loginaudit = Get-LoginAuditing -sqlinstance "$instance" Write-Host "Evaluating Configuration Parameters for Security Risks ....`r`n" $config = Get-SQLGlobalSettings -sqlinstance "$instance" Write-Host "[$(Get-date -DisplayHint date -Format g)]Finished Script Execution. Formatting Report ....`r`n" #Final Reporting $css = @" <style> body { background-color:white; font-family: "Times New Roman", Times, serif; font-size:11pt; color:#333; } .header { font-size: 40px; color: white; text-align: center; background: black; font-family: "Times New Roman", Times, serif; font-weight: bold; width: 100%; border: 1px black; margin: 0;} h1 {font-size:40px;color: black;} h2 {font-size:30px;color: black;} h4 {font-size:15px;color: black;} p {font-size:30px;color: black;} table {margin-left:50px; table-layout: fixed;} td {word-wrap:break-word;} td , th { border:2px solid black; border-collapse:collapse; word-wrap:break-word; } th { font-family: "Times New Roman", Times, serif; font-size:14pt; color:white; background-color:black; font-weight:bold; } table, tr, td, th {padding: 2px; margin: 1px;} </style> "@ if($serverinfo){ $serverinfo = $serverinfo | select ServerType, "ServerType(PhysicalorVirtual)", MachineName, Technology, InstanceName, ServiceAccount, @{n="Version";e={if($_.Version -like '*2005*'){'<td bgcolor="#FF0000">'+$_.Version} elseif($_.Version -like '*2000*'){'<td bgcolor="#FF0000">'+$_.Version} elseif($_.Version -like '*2008*'){'<td bgcolor="#FFFF00">'+$_.Version} else{'<td bgcolor="#00CC33">'+$_.Version}}}, @{n="Edition";e={if($_.Edition -like '*32*bit'){'<td bgcolor="#FF0000">'+$_.Edition} elseif($_.Edition -notlike '*Enterprise*'){'<td bgcolor="#FFFF00">'+$_."Edition"} else{'<td bgcolor="#00CC33">'+$_."Edition"}}}, Databases, @{n="Default DataFile Location";e={if($_.data -like "$log*"){'<td bgcolor="#FF0000">'+$_.data}else{'<td bgcolor="#00CC33">'+$_.data} }}, @{n="Default LogFile Path";e={if($_.log -like "$data*"){'<td bgcolor="#FF0000">'+$_.log}else{'<td bgcolor="#00CC33">'+$_.log}}}, @{n="TempDB Datafile Path";e={if($_.tempdbdata -like '$data*' -or $_.tempdbdata -like '$log*' ){'<td bgcolor="#FF0000">'+$_.tempdbdata}else{'<td bgcolor="#00CC33">'+$_.tempdbdata}}}, @{n="TempDB Logfile Path";e={if($_.tempdblog -like '$log*' -or $_.tempdblog -like '$data*'){'<td bgcolor="#FF0000">'+$_.tempdblog}else{'<td bgcolor="#00CC33">'+$_.tempdblog}}}, @{n="Root Path";e={$_.root}}, ErrorLogPath $htmlreport = $serverinfo | ConvertTo-HTML -PreContent "<h2><center>SQL Server Information</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport = ConvertTo-HTML -PreContent "<h2><center>SQL Server Information</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($dbowners){ $dbowners = $dbowners | select Database , Owner, @{n="Owner Type";e={if($_."Owner Type" -like '*SQL*') {'<td bgcolor="#00CC33">'+$_."Owner Type"} else {'<td bgcolor="#FF0000">'+$_."Owner Type"} }} $htmlreport += $dbowners | ConvertTo-HTML -PreContent "<h2><center>Database Owners</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>Database Ownners</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($winauth){ $winauth = $winauth | select Login, Type, @{n="Disabled?";e={if($_.Login -like 'NT *' -and $_."Disabled?" -like 'N') {'<td bgcolor="#FF0000">'+$_."Disabled?"} elseif($_."Disabled?" -like 'Y'){'<td bgcolor="#FF0000">'+$_."Disabled?"} else {'<td bgcolor="#00CC33">'+$_."Disabled?"}}}, "Create Date", @{n="Default Database";e={if($_."Default Database" -like 'master'){'<td bgcolor="#00CC33">'+$_."Default Database"}else{'<td bgcolor="#FF0000">'+$_."Default Database"}}} $htmlreport += $winauth | ConvertTo-HTML -PreContent "<h2><center>Windows Authentication Logins</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>Windows Authentication Logins</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($sqlauth){ $sqlauth = $sqlauth | select Login, Type, @{n="Disabled?";e={if($_.Login -like '##*' -and $_."Disabled?" -like 'Y') {'<td bgcolor="#00CC33">'+$_."Disabled?"} elseif($_.Login -like '##*' -and $_."Disabled?" -like 'N') {'<td bgcolor="#FF0000">'+$_."Disabled?"} elseif($_."Disabled?" -like 'Y'){'<td bgcolor="#FF0000">'+$_."Disabled?"} else {'<td bgcolor="#00CC33">'+$_."Disabled?"}}} ,"Create Date", "Default Database", "Enforce Password Policy" , "Enforce Password Expiration" $htmlreport += $sqlauth | ConvertTo-HTML -PreContent "<h2><center>SQL Server Authentication Logins</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>SQL Server Authentication Logins</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($serlvlperm){ $serlvlperm = $serlvlperm | select "Grantee Login", Type, "Grantor Login", Permission, State $htmlreport += $serlvlperm | ConvertTo-HTML -PreContent "<h2><center>Server Level Permissions</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>Server Level Permissions</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($serrolmem){ $serrolmem = $serrolmem | select Login, @{n="Role";e={if($_."Role" -like 'sysadmin'){'<td bgcolor="#FFFF00">'+$_."Role"}else{$_."Role"}}} $htmlreport += $serrolmem | ConvertTo-HTML -PreContent "<h2><center>Server Role Members</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>Server Role Memebers</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($dblvlperm){ $dblvlperm = $dblvlperm | select Database, "Grantee Login", Type, "Grantor Login", Permission, "Applies To", State $htmlreport += $dblvlperm | ConvertTo-HTML -PreContent "<h2><center>Database Level Permissions</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>Database Level Permissions</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($dbrolmem){ $dbrolmem = $dbrolmem | select Database, Login,@{n="Role";e={if($_."Role" -like 'db_owner'){'<td bgcolor="#FFFF00">'+$_."Role"}else{$_."Role"}}} $htmlreport += $dbrolmem | ConvertTo-HTML -PreContent "<h2><center>Database Role Members</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>Database Role Members</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($jobowners){ $jobowners = $jobowners | select Job, @{n="Owner";e={if($_.Owner -like 'sa' ){'<td bgcolor="#00CC33">'+$_.Owner} else{'<td bgcolor="#FF0000">'+$_.Owner} }}, "Login Type" $htmlreport += $jobowners | ConvertTo-HTML -PreContent "<h2><center>SQL Server Job Owners</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>SQL Server Job Owners</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($servaccount){ $servaccount = $servaccount | select @{n="Service Name";e={$_.caption}}, @{n="Service Account";e={if($_.startname -like 'Local*' -or $_.startname -like 'NT*'){'<td bgcolor="#FF0000">'+$_.startname}else{'<td bgcolor="#00CC33">'+$_.startname}}}, StartMode $htmlreport += $servaccount | ConvertTo-HTML -PreContent "<h2><center>Login Account for SQL Services</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>Login Account for SQL Services</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($sqlprotocols){ $sqlprotocols = $sqlprotocols | select "Protocol Name", @{n="IsEnabled?";e={if($_."Protocol Name" -like 'Named*' -or $_."Protocol Name" -like 'VIA' ){if($_.IsEnabled -like 'True'){'<td bgcolor="#FF0000">'+$_.IsEnabled} else{'<td bgcolor="#00CC33">'+$_.IsEnabled}} else{'<td bgcolor="#00CC33">'+$_.IsEnabled}}} $htmlreport += $sqlprotocols | ConvertTo-HTML -PreContent "<h2><center>SQL Server Network Protocols</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>SQL Server Network Protocols</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($tcpports){ $statictcpports = @{} $dynamictcpports = @{} $statictcpports = $tcpports | Where-Object {$_.Name -like 'TcpPort'} | Select Value $dynamictcpports = $tcpports | Where-Object {$_.Name -like 'TcpDynamicPorts'} | Select Value $dport = $($dynamicportvalue).Value $sport = $($statictcpports).Value if ($sport) { $htmlreport += ConvertTo-HTML -PreContent "<h2><center>SQL TCP Port Settings</center></h2><br><br>" -PostContent "<p><mark>SQL Server Instance [$instance] is set to use Static Port # : $sport.</mark></p><br>" -Fragment | out-string } else { $htmlreport += ConvertTo-HTML -PreContent "<h2><center>SQL TCP Port Settings</center></h2><br><br>" -PostContent "<p><mark>SQL Server Instance [$instance] is set to use Dynamic # : $dport.</mark><p><br>" -Fragment | out-string } } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>SQL TCP Port Settings</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($loginaudit){ $loginaudit = $loginaudit | select @{n="Login Auditing";e={$_.LoginAuditing}}, @{n="Recommended Setting";e={if($_.LoginAuditing -like 'Failed Logins Only'){'<td bgcolor="#00CC33">'+"Y"}else{'<td bgcolor="#FF0000">'+"N"}}} $htmlreport += $loginaudit | ConvertTo-HTML -PreContent "<h2><center>SQL Server Login Auditing</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>SQL Server Login Auditing</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } if($config){ $config = $config | select @{n="Configuration Parameter";e={$_.s_name}}, @{n="IsEnabled?";e={if($_.CurrentSetting -like 'Enabled'){'<td bgcolor="#FF0000">'+$_.CurrentSetting}else{'<td bgcolor="#00CC33">'+$_.CurrentSetting} }} $htmlreport += $config | ConvertTo-HTML -PreContent "<h2><center>SQL Sever Global Configuration Parameters</center></h2><br><br>" -Fragment | out-string } else{ $htmlreport += ConvertTo-HTML -PreContent "<h2><center>SQL Sever Global Configuration Parameters</center></h2><br>" -PostContent "<h4><center>Error Encountered in reporting the details. Check the script log for details. If nothing is reported in the log then no details exist for this collector.</center></h4><br>" -Fragment | out-string } $htmlreport = $htmlreport.Replace('<td><td bgcolor="#FFFF00">','<td bgcolor="#FFFF00">') $htmlreport = $htmlreport.Replace('<td><td bgcolor="#FF0000">','<td bgcolor="#FF0000">') $htmlreport = $htmlreport.Replace('<td><td bgcolor="#00CC33">','<td bgcolor="#00CC33">') $body = convertto-html -Head $css -PostContent "$htmlreport <br/><br/><h1 class = `"header`">END OF REPORT</h1>" -body "<h1 class = `"header`">SQL SERVER SECURITY REVIEW</h1><center><h4>Report Generated on $(Get-date -DisplayHint date -Format g)</h4></center>" -Title "SQL SERVER SECURITY REVIEW" | out-string $body = $body.Replace("<table>`r`n</table>","") $body | Out-File "$report" <# .SYNOPSIS The Script retrives the Security Best Practises for SQL Server and put's all that info in a html document. .DESCRIPTION The Following information collectors can be found in the report. 1. Server Information 2. Database owners 3. Windows Authenticated Logins 4. SQL Authenticated Logins 5. Server Level Permissions 6. Server Role Members 7. Database Level Permissions 8. Database Role Members 9. Job Owners 10. Login Account for SQL Services 11. SQL Server Network Protocols 12. SQL Server TCP Port 13. SQL Server Login Auditing Property Setting 14. SQL Server Global Configuration Parameter (Secrity Affecting One's) .PARAMETER Computer Specify the Computer\Machine\Sever Name here to make IO analysis for the Server. One hostname per run. For Example: Get-SQLSecurityReview -computer ServerName ...... .PARAMETER instance Specify the SQL instance Name to make IO analysis for the Server. One SQL instance per run. For Example: Get-SQLSecurityReview ...... -instance ServerName\SQLInstance ...... .PARAMETER report Specify the location for the output report For Example: Get-SQLSecurityReview ...... C:\temp\ServerName$SQLInstance-Security_Review.html .EXAMPLE Get-SQLSecurityReview -computer ServerName -instance ServerName\SQLInstance -report C:\temp\ServerName$SQLInstance-Security_Review.html This will generate a html color coded report for localhost with sql instance jupiter and will dump the report @ location - C:\temp\ServerName$SQLInstance-Security_Review.html .NOTES Author: Sandeep Arora arora@pythian.com sandeep16arora@gmail.com Version Info: 1.1 - 01/15/2016 - Initial Draft 1.2 - 06/25/2016 - Bug Fixes with SMO Objects for Collecting SQL Server Port Info and Details for Networking Protocols #>

email

Authors Sandeep Arora

Interested in working with Sandeep? Schedule a tech call.