Display the access configuration status of SQL Server Database securables on Windows.
(There’s also a management pack with agents tasks located here.)
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
2 Replies to “Verify SQL Server Securables with PowerShell”
Hi
I’m getting error: “Could not open SQL connection” when trying to execute task/script.
Can see that this is from the try/catch when connecting to the database. Not much to go on though. We are running Kevin Holmans SQL MP Run As solution. Any ideas whats wrong?
@Peter,
Hi Peter, when running the script you could try adding the error variable to the Catch block (within the Invoke-CLSqlCmd function) to reveal the Exception data.
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
Write-Host “Exception Data: $($_ | select -Property *)” -F Yellow
Exit 1
}