Verify SQL Server Securables with PowerShell

Display the access configuration status of SQL Server Database securables on Windows.
(There’s also a management pack with agents tasks located here.)

Download “Verify SQL Server Securables (PowerShell)” SQLSecurables_2021.08.26.1647.zip – Downloaded 26 times – 7 KB

DBEngine Securables

<#
.DESCRIPTION
  Returns DBEngine securables info.
  This will retrieve the same data as the Securables monitor found in this MP: Microsoft.SQLServer.Windows.Module.Monitoring, Version=7.0.32.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, Microsoft.SQLServer.Windows.DBEngine.Configuration.SecurablesConfigMonitor
.EXAMPLE
   .\Show-SecurablesServerConfig.ps1 -SQLServerName 'SQL24.CONTOSO.COM'
.NOTES
    Script: Show-SecurablesServerConfig.ps1
    Author: Tyson Paul
    Web: https://monitoringguys.com/
    Version History:
    2021.08.28.1647 - v1. Enjoy.

#>
Param (
    [CmdletBinding(DefaultParameterSetName='Parameter Set 1', 
                  SupportsShouldProcess=$true, 
                  PositionalBinding=$false,
                  HelpUri = 'http://www.microsoft.com/',
                  ConfirmImpact='Medium')]
    [Alias()]
    [OutputType([System.Collections.ArrayList])]

  # Target SQL Server to query
  $SQLServerName = 'LOCALHOST'
)

$list = @'
"VIEW SERVER STATE", SecurablesConfigMonitorBase.PermType.Undefined, SecurablesConfigMonitorBase.SecurableType.Server
"VIEW ANY DEFINITION", SecurablesConfigMonitorBase.PermType.Undefined, SecurablesConfigMonitorBase.SecurableType.Server
"VIEW ANY DATABASE", SecurablesConfigMonitorBase.PermType.Undefined, SecurablesConfigMonitorBase.SecurableType.Server
"sys.dm_os_performance_counters", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_tran_active_transactions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_tran_session_transactions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_tran_active_transactions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_tran_session_transactions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_exec_sessions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_exec_requests", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_exec_connections", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_os_sys_info", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_os_ring_buffers", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_os_volume_stats", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_hadr_database_replica_states", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_server_services", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_db_xtp_checkpoint_files", SecurablesConfigMonitorBase.PermType.Select, 12, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_db_xtp_table_memory_stats", SecurablesConfigMonitorBase.PermType.Select, 12, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_resource_governor_resource_pools", SecurablesConfigMonitorBase.PermType.Select, 12, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_db_xtp_hash_index_stats", SecurablesConfigMonitorBase.PermType.Select, 12, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_os_threads", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.databases", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"master.sys.master_files", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.sysprocesses", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.availability_replicas", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.database_mirroring", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.configurations", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"msdb.dbo.syspolicy_policies", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"msdb.dbo.syspolicy_conditions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"msdb.dbo.syspolicy_policy_execution_history", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"msdb.dbo.syspolicy_policy_execution_history_details", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"msdb.dbo.sysjobschedules", SecurablesConfigMonitorBase.PermType.Select, SecurablesConfigMonitorBase.SecurableType.Table
"msdb.dbo.log_shipping_primary_databases", SecurablesConfigMonitorBase.PermType.Select, SecurablesConfigMonitorBase.SecurableType.Table
"msdb.dbo.log_shipping_secondary_databases", SecurablesConfigMonitorBase.PermType.Select, SecurablesConfigMonitorBase.SecurableType.Table
"msdb.dbo.backupset", SecurablesConfigMonitorBase.PermType.Select, SecurablesConfigMonitorBase.SecurableType.Table
"sys.sp_enumerrorlogs", SecurablesConfigMonitorBase.PermType.Execute, SecurablesConfigMonitorBase.SecurableType.StoredProc
"sys.xp_readerrorlog", SecurablesConfigMonitorBase.PermType.Execute, SecurablesConfigMonitorBase.SecurableType.StoredProc
"msdb.dbo.sp_help_jobactivity", SecurablesConfigMonitorBase.PermType.Execute, SecurablesConfigMonitorBase.SecurableType.StoredProc
"sys.xp_instance_regread", SecurablesConfigMonitorBase.PermType.Execute, SecurablesConfigMonitorBase.SecurableType.StoredProc
"msdb.dbo.sp_help_job", SecurablesConfigMonitorBase.PermType.Execute, SecurablesConfigMonitorBase.SecurableType.StoredProc
"master.sys.dm_hadr_availability_group_states", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"master.sys.dm_hadr_availability_replica_states", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"master.sys.dm_hadr_database_replica_cluster_states", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"master.sys.dm_hadr_database_replica_states", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_object_sets", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_policy_categories", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_target_sets", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_target_set_levels", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"sys.dm_os_host_info", SecurablesConfigMonitorBase.PermType.Select, 14, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_configuration", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_system_health_state", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"sys.availability_groups", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"sys.availability_replicas", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
'@ -split '\n'

#---------------------------------------------------------------------

Function Invoke-CLSqlCmd 
{
  Param(
    [string] $Server,
    [string] $DB,
    [string] $SQLCMD,
    [int]$SQLCMDTimeoutSeconds = 10,
    [int]$ConnectionTimeout = 5
  )
  
  Try 
  {
    $ConnectionString = "Server=$Server;Database=$DB;Trusted_Connection=True;Timeout=$ConnectionTimeout"
    $Connection = New-Object -TypeName system.data.SqlClient.SQLConnection -ArgumentList ($ConnectionString)
    $Command = New-Object -TypeName system.data.sqlclient.sqlcommand -ArgumentList ($SQLCMD, $Connection)
    $Command.CommandTimeout = $SQLCMDTimeoutSeconds
    $Connection.Open()
    Try 
    {
      $Adapter = New-Object -TypeName System.Data.sqlclient.sqlDataAdapter -ArgumentList $Command
      $DtaSet = New-Object -TypeName System.Data.DataSet
      $Null = $Adapter.Fill($DtaSet)
    }
    Catch
    {
      $script:msg = 'Could not fill dataset!' #Do not alter this message, it may be string-matched in other areas.
      Write-Error $script:msg
      Exit 1
    }
  }
  Catch
  {
    $script:msg = 'Could not open SQL connection!' #Do not alter this message, it may be string-matched in other areas.
    Write-Error $script:msg
    Exit 1
  }
  Finally {
    $Connection.Close()
  }

  # Add the ConnectionString to the returned data object so that it can be included in the property bag for more visibility.
  Return $DtaSet.Tables
} #endregion Invoke-CLSqlCmd

#---------------------------------------------------------------------

Function BuildQuery {
  Param (
    $x
  )
  
  
  $Query = @"
--GetServerSecurables

DECLARE @usr NVARCHAR(128)  
SET @usr = user

DECLARE @xmlDoc XML = CAST('
$x
' AS XML);


DECLARE @TargetSecurables TABLE(
	[Name] NVARCHAR(128)
	,[PermissionType] NVARCHAR(30)
	,[SecurableType] INT
	,[SqlCondition] INT
);

INSERT INTO @TargetSecurables
SELECT 
	xc.value('./name[1]', 'NVARCHAR(128)') AS [Name]
	,xc.value('./pt[1]', 'NVARCHAR(30)') AS [PermissionType]
	,xc.value('./st[1]', 'INT') AS [SecurableType]
	,xc.value('./cn[1]', 'INT') AS [SqlCondition]
FROM @xmlDoc.nodes('/./dmv') AS xt(xc);


DECLARE @IsHadrEnabled INT = CAST(SERVERPROPERTY('IsHadrEnabled') AS INT);

WITH src AS(
	SELECT
		tObj.[Name]
		,tObj.SecurableType
		,CASE tObj.SecurableType
			WHEN 0 THEN HAS_PERMS_BY_NAME(NULL, NULL, tObj.[Name])
			ELSE HAS_PERMS_BY_NAME(tObj.[Name], 'OBJECT', tObj.PermissionType)
		END AS HasPermission
	FROM @TargetSecurables tObj
	WHERE tObj.SqlCondition = 0 OR (tObj.SqlCondition = 1 AND @IsHadrEnabled = 1)
)
SELECT
	src.[Name] AS [Name]
	--,SecurableType AS [SecurableType]
	,CASE SecurableType
		WHEN 0 THEN 'Server-level permissions'
		WHEN 1 THEN 'Views'
		WHEN 2 THEN 'Stored procedures'
		WHEN 3 THEN 'Tables'
		ELSE 'NO DATA'
		END AS [SecurableType]
	, CASE src.HasPermission 
		WHEN 0 THEN 'FALSE'
		WHEN 1 THEN 'TRUE'
		ELSE 'NO DATA'
	END AS [HasPermission]
  ,@usr AS UserName
FROM src
--WHERE src.HasPermission = 0;

"@

  Return $Query
}

#---------------------------------------------------------------------
# CLASS
Class cSecurable {
  [string]$Name
  [string]$PermTypeName
  [int]$MinSqlServerMajorVersion
  [string]$SecurableTypeName
  [int]$SqlCondition
  [string]$xml
  $PermType = @{
    'SecurablesConfigMonitorBase.PermType.Select' = "SELECT"
    'SecurablesConfigMonitorBase.PermType.Execute' = "EXECUTE"
    'SecurablesConfigMonitorBase.PermType.Undefined' = "Undefined"
  }
  
  $SecurableType = @{
    'SecurablesConfigMonitorBase.SecurableType.Server' = 0
    'SecurablesConfigMonitorBase.SecurableType.View' = 1
    'SecurablesConfigMonitorBase.SecurableType.StoredProc' = 2
    'SecurablesConfigMonitorBase.SecurableType.Table' = 3
  }
  
  
  cSecurable (
    [string]$Name,
    [string]$PermTypeName,
    [string]$SecurableTypeName
  )
  {
    $this.Name = $Name.Trim()
    $this.PermTypeName = $PermTypeName.Trim()
    $this.SecurableTypeName = $SecurableTypeName.Trim()
    $this.MinSqlServerMajorVersion = 11
    $this.SqlCondition = 0
    
    $this.ConvertToXml()
  }
  
  cSecurable (
    [string]$Name,
    [string]$PermTypeName,
    [int]$MinSqlServerMajorVersion,
    [string]$SecurableTypeName,
    [int]$SqlCondition
  )
  {
    $this.Name = $Name.Trim()
    $this.PermTypeName = $PermTypeName.Trim()
    $this.SecurableTypeName = $SecurableTypeName.Trim()
    $this.MinSqlServerMajorVersion = $MinSqlServerMajorVersion
    $this.SqlCondition = $SqlCondition

    $this.ConvertToXml()
  }
  
  # ------------- Functions
  [string]ConvertToXml(){
    $x = @"
<dmv>
<name>$($this.Name.Replace('"',''))</name>
<pt>$($this.PermType[$this.PermTypeName])</pt>
<st>$($this.SecurableType[$this.SecurableTypeName])</st>
<cn>$($this.SqlCondition)</cn> 
</dmv>
"@
    $this.xml = $x
    Return $x
  }

} #end Class
#---------------------------------------------------------------------
Class Result {
  [string]$Name
  $SecurableType
  $HasPermission
  [string]$UserName
  
  Result (
    [string]$Name,
    $SecurableType,
    $HasPermission,
    [string]$UserName
  )
  {
    $this.Name = $Name.Trim()
    $this.HasPermission = $HasPermission
    $this.SecurableType = $SecurableType
    $this.UserName = $UserName.Trim()
  }
}
#---------------------------------------------------------------------

$DBName = 'master'

[System.Collections.ArrayList]$arr = @()
ForEach ($line in $list) {
  $params = $line.split(',')
  If ($params.count -eq 3) {
    [cSecurable]$obj = [cSecurable]::New($params[0],$params[1],$params[2])
  }
  If ($params.count -gt 3) {
    [cSecurable]$obj =     [cSecurable]::New($params[0],$params[1],$params[2],$params[3],$params[4])
  }

  $Query = BuildQuery -x $obj.xml
   
  $Row = Invoke-CLSqlCmd -Server $SQLServerName -Database $DBName -SQLCMD $Query
  $Row | ForEach-Object { 
    [Result]$Result = [Result]::New($_.Name,$_.SecurableType,$_.HasPermission,$_.UserName)
    $NULL = $arr.Add($Result) 
  }
}

Return $arr


Database Securables

<#
.DESCRIPTION
  Returns DATABASE securables info.
  This will retrieve the same data as the Securables monitor found in this MP: Microsoft.SQLServer.Windows.Module.Monitoring, Version=7.0.32.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, Microsoft.SQLServer.Windows.DBEngine.Configuration.SecurablesConfigMonitor
.EXAMPLE
    .\Show-SecurablesDBConfig.ps1 -SQLServerName 'SQL24.CONTOSO.COM'
.NOTES
    Script: Show-SecurablesDBConfig.ps1
    Author: Tyson Paul
    Web: https://monitoringguys.com/
    Version History:
    2021.08.28.1647 - v1. Enjoy.

#>

Param (
    [CmdletBinding(DefaultParameterSetName='Parameter Set 1', 
                  SupportsShouldProcess=$true, 
                  PositionalBinding=$false,
                  HelpUri = 'http://www.microsoft.com/',
                  ConfirmImpact='Medium')]
    [Alias()]
    [OutputType([System.Collections.ArrayList])]

  # Target SQL Server to query
  $SQLServerName = 'LOCALHOST'
)


$list = @'
"VIEW SERVER STATE", SecurablesConfigMonitorBase.PermType.Undefined, SecurablesConfigMonitorBase.SecurableType.Server
"VIEW ANY DEFINITION", SecurablesConfigMonitorBase.PermType.Undefined, SecurablesConfigMonitorBase.SecurableType.Server
"VIEW ANY DATABASE", SecurablesConfigMonitorBase.PermType.Undefined, SecurablesConfigMonitorBase.SecurableType.Server
"sys.dm_os_performance_counters", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_tran_active_transactions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_tran_session_transactions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_tran_active_transactions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_tran_session_transactions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_exec_sessions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_exec_requests", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_exec_connections", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_os_sys_info", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_os_ring_buffers", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_os_volume_stats", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_hadr_database_replica_states", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_server_services", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_db_xtp_checkpoint_files", SecurablesConfigMonitorBase.PermType.Select, 12, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_db_xtp_table_memory_stats", SecurablesConfigMonitorBase.PermType.Select, 12, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_resource_governor_resource_pools", SecurablesConfigMonitorBase.PermType.Select, 12, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_db_xtp_hash_index_stats", SecurablesConfigMonitorBase.PermType.Select, 12, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.dm_os_threads", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.databases", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"master.sys.master_files", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.sysprocesses", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.availability_replicas", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.database_mirroring", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"sys.configurations", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"msdb.dbo.syspolicy_policies", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"msdb.dbo.syspolicy_conditions", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"msdb.dbo.syspolicy_policy_execution_history", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"msdb.dbo.syspolicy_policy_execution_history_details", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 0
"msdb.dbo.sysjobschedules", SecurablesConfigMonitorBase.PermType.Select, SecurablesConfigMonitorBase.SecurableType.Table
"msdb.dbo.log_shipping_primary_databases", SecurablesConfigMonitorBase.PermType.Select, SecurablesConfigMonitorBase.SecurableType.Table
"msdb.dbo.log_shipping_secondary_databases", SecurablesConfigMonitorBase.PermType.Select, SecurablesConfigMonitorBase.SecurableType.Table
"msdb.dbo.backupset", SecurablesConfigMonitorBase.PermType.Select, SecurablesConfigMonitorBase.SecurableType.Table
"sys.sp_enumerrorlogs", SecurablesConfigMonitorBase.PermType.Execute, SecurablesConfigMonitorBase.SecurableType.StoredProc
"sys.xp_readerrorlog", SecurablesConfigMonitorBase.PermType.Execute, SecurablesConfigMonitorBase.SecurableType.StoredProc
"msdb.dbo.sp_help_jobactivity", SecurablesConfigMonitorBase.PermType.Execute, SecurablesConfigMonitorBase.SecurableType.StoredProc
"sys.xp_instance_regread", SecurablesConfigMonitorBase.PermType.Execute, SecurablesConfigMonitorBase.SecurableType.StoredProc
"msdb.dbo.sp_help_job", SecurablesConfigMonitorBase.PermType.Execute, SecurablesConfigMonitorBase.SecurableType.StoredProc
"master.sys.dm_hadr_availability_group_states", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"master.sys.dm_hadr_availability_replica_states", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"master.sys.dm_hadr_database_replica_cluster_states", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"master.sys.dm_hadr_database_replica_states", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_object_sets", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_policy_categories", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_target_sets", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_target_set_levels", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"sys.dm_os_host_info", SecurablesConfigMonitorBase.PermType.Select, 14, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_configuration", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"msdb.dbo.syspolicy_system_health_state", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"sys.availability_groups", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
"sys.availability_replicas", SecurablesConfigMonitorBase.PermType.Select, 11, SecurablesConfigMonitorBase.SecurableType.View, 1
'@ -split '\n'

#---------------------------------------------------------------------

Function Invoke-CLSqlCmd 
{
  Param(
    [string] $Server,
    [string] $DB,
    [string] $SQLCMD,
    [int]$SQLCMDTimeoutSeconds = 10,
    [int]$ConnectionTimeout = 5
  )
  
  Try 
  {
    $ConnectionString = "Server=$Server;Database=$DB;Trusted_Connection=True;Timeout=$ConnectionTimeout"
    $Connection = New-Object -TypeName system.data.SqlClient.SQLConnection -ArgumentList ($ConnectionString)
    $Command = New-Object -TypeName system.data.sqlclient.sqlcommand -ArgumentList ($SQLCMD, $Connection)
    $Command.CommandTimeout = $SQLCMDTimeoutSeconds
    $Connection.Open()
    Try 
    {
      $Adapter = New-Object -TypeName System.Data.sqlclient.sqlDataAdapter -ArgumentList $Command
      $DtaSet = New-Object -TypeName System.Data.DataSet
      $Null = $Adapter.Fill($DtaSet)
    }
    Catch
    {
      $script:msg = 'Could not fill dataset!' #Do not alter this message, it may be string-matched in other areas.
      Write-Error $script:msg
      Exit 1
    }
  }
  Catch
  {
    $script:msg = 'Could not open SQL connection!' #Do not alter this message, it may be string-matched in other areas.
    Write-Error $script:msg
    Exit 1
  }
  Finally {
    $Connection.Close()
  }

  # Add the ConnectionString to the returned data object so that it can be included in the property bag for more visibility.
  Return $DtaSet.Tables
} #endregion Invoke-CLSqlCmd

#---------------------------------------------------------------------
Function BuildQuery {
  Param (
    $x
  )
  
  
  $Query = @"
DECLARE @usr NVARCHAR(128)  
SET @usr = user

DECLARE @xmlDoc XML = CAST('
$x
' AS XML);

DECLARE @dbName sysname, @sql nvarchar(2000)

IF OBJECT_ID ('tempdb.dbo.#TargetDbSecurables') IS NOT NULL DROP TABLE #TargetDbSecurables;
IF OBJECT_ID ('tempdb.dbo.#ResultTable') IS NOT NULL DROP TABLE #ResultTable;

CREATE TABLE #TargetDbSecurables(
	[Name] NVARCHAR(128)
	,[PermissionType] NVARCHAR(30)
	,[SecurableType] INT
	,[UserName] NVARCHAR (128)

);

INSERT INTO #TargetDbSecurables
SELECT 
	xc.value('./name[1]', 'NVARCHAR(128)') AS [Name]
	,xc.value('./pt[1]', 'NVARCHAR(30)') AS [PermissionType]
	,xc.value('./st[1]', 'INT') AS [SecurableType]
	,@usr AS [UserName]
FROM @xmlDoc.nodes('/./dmv') AS xt(xc);


CREATE TABLE #ResultTable (
	[DatabaseName] SYSNAME
	,[Name] NVARCHAR(128)
	,[SecurableType] INT
	,[HasIssue] BIT
	,[UserName] NVARCHAR (128)
	)

DECLARE dbCursor CURSOR LOCAL FOR 
SELECT 
	[name] 
	FROM sys.databases

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@Fetch_Status=0 BEGIN
	BEGIN TRY
		SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
WITH src AS(
	SELECT
		tObj.[Name]
		,tObj.SecurableType
		,CASE tObj.SecurableType
			WHEN 0 THEN HAS_PERMS_BY_NAME(NULL, NULL, tObj.[Name])
			ELSE HAS_PERMS_BY_NAME(tObj.[Name], ''OBJECT'', tObj.PermissionType)
		END AS HasPermission
		,tObj.UserName AS UserName
	FROM #TargetDbSecurables tObj
)
SELECT
	DB_NAME() AS [DatabaseName]
	,src.[Name] AS [Name]
	,SecurableType AS [SecurableType]
		,CASE src.HasPermission
	WHEN 1 THEN CAST(0 AS BIT)
	ELSE CAST(1 AS BIT) END AS [HasIssue]
	,src.[UserName] AS UserName
FROM src
--WHERE src.HasPermission = 0;
'
		INSERT INTO #ResultTable
		EXEC sp_executesql @sql

		IF @@ROWCOUNT = 0
			INSERT INTO #ResultTable(DatabaseName,[Name],SecurableType,HasIssue,UserName) VALUES(@dbName, NULL, 0, CAST(0 AS BIT),@usr)
			
	END TRY
	BEGIN CATCH
	END CATCH;
	
	FETCH NEXT FROM dbCursor INTO @dbName;
END;

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #ResultTable

DROP TABLE #TargetDbSecurables;
DROP TABLE #ResultTable;

"@

  Return $Query
}

#---------------------------------------------------------------------
# CLASSES
#---------------------------------------------------------------------
Class cSecurable {
  [string]$Name
  [string]$PermTypeName
  [int]$MinSqlServerMajorVersion
  [string]$SecurableTypeName
  [int]$SqlCondition
  [string]$xml
  $PermType = @{
    'SecurablesConfigMonitorBase.PermType.Select' = "SELECT"
    'SecurablesConfigMonitorBase.PermType.Execute' = "EXECUTE"
    'SecurablesConfigMonitorBase.PermType.Undefined' = "Undefined"
  }
  
  $SecurableType = @{
    'SecurablesConfigMonitorBase.SecurableType.Server' = 0
    'SecurablesConfigMonitorBase.SecurableType.View' = 1
    'SecurablesConfigMonitorBase.SecurableType.StoredProc' = 2
    'SecurablesConfigMonitorBase.SecurableType.Table' = 3
  }
  
  
  cSecurable (
    [string]$Name,
    [string]$PermTypeName,
    [string]$SecurableTypeName
  )
  {
    $this.Name = $Name.Trim()
    $this.PermTypeName = $PermTypeName.Trim()
    $this.SecurableTypeName = $SecurableTypeName.Trim()
    $this.MinSqlServerMajorVersion = 11
    $this.SqlCondition = 0
    
    $this.ConvertToXml()
  }
  
  cSecurable (
    [string]$Name,
    [string]$PermTypeName,
    [int]$MinSqlServerMajorVersion,
    [string]$SecurableTypeName,
    [int]$SqlCondition
  )
  {
    $this.Name = $Name.Trim()
    $this.PermTypeName = $PermTypeName.Trim()
    $this.SecurableTypeName = $SecurableTypeName.Trim()
    $this.MinSqlServerMajorVersion = $MinSqlServerMajorVersion
    $this.SqlCondition = $SqlCondition

    $this.ConvertToXml()
  }
  
  # ------------- Functions
  [string]ConvertToXml(){
    $x = @"
<dmv>
<name>$($this.Name.Replace('"',''))</name>
<pt>$($this.PermType[$this.PermTypeName])</pt>
<st>$($this.SecurableType[$this.SecurableTypeName])</st>
<cn>$($this.SqlCondition)</cn> 
</dmv>
"@
    $this.xml = $x
    Return $x
  }

} #end Class
#---------------------------------------------------------------------
Class Result {
  $DatabaseName
  $PermissionName
  $HasIssue
  $SecurableType
  $SecurableTypeName
  
  Result (
    [string]$DatabaseName,
    [string]$PermissionName,
    $HasIssue,
    $SecurableType,
    $SecurableTypeName
  )
  {
    $this.DatabaseName = $DatabaseName.Trim()
    $this.PermissionName = $PermissionName.Trim()
    $this.HasIssue = $HasIssue
    $this.SecurableType = $SecurableType
    $this.SecurableTypeName = $SecurableTypeName
  }
}
#---------------------------------------------------------------------

$DBName = 'master'
$tmpSecurableTypeName = $Null
[System.Collections.ArrayList]$arr = @()

$hashSecurableType = @{
  'SecurablesConfigMonitorBase.SecurableType.Server' = "Server-level permissions"
  'SecurablesConfigMonitorBase.SecurableType.View' = "Views"
  'SecurablesConfigMonitorBase.SecurableType.StoredProc' = "Stored procedures"
  'SecurablesConfigMonitorBase.SecurableType.Table' =  "Tables"  
}


ForEach ($line in $list) {
  $params = $line.split(',')
  If ($params.count -eq 3) {
    [cSecurable]$obj = [cSecurable]::New($params[0],$params[1],$params[2])
    $tmpSecurableTypeName = [string]$params[2].Trim()
  }
  If ($params.count -gt 3) {
    [cSecurable]$obj =     [cSecurable]::New($params[0],$params[1],$params[2],$params[3],$params[4])
    $tmpSecurableTypeName = [string]$params[3].Trim()
  }
  $Row = $NULL
  $Query = BuildQuery -x $obj.xml
  $Row = Invoke-CLSqlCmd -Server $SQLServerName -Database $DBName -SQLCMD $Query
  $Row  | % { 
    [Result]$Result = [Result]::New($_.DatabaseName,$_.Name,$_.HasIssue,$_.SecurableType,($hashSecurableType[$tmpSecurableTypeName]))
    $NULL = $arr.Add($Result) 
  }
}

Return $arr

Leave a Reply

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