Quick script to list encrypted objects in sql server. Nothing fancy.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.smo') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
$srvConn = New-Object('Microsoft.SqlServer.Management.Common.ServerConnection') ('serverName')
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $srvConn
$objects = @();
$srv.Databases | %{
$dbName = $_.Name
$_.StoredProcedures | where{$_.IsEncrypted -and -not $_.IsSystemObject} | %{
$obj = New-Object -TypeName PSObject -Property @{
DatabaseName = $dbName
ObjectType = 'Stored Procedure'
ObjectName = $_.Name
}
$objects += $obj;
}
$_.Views | where{$_.IsEncrypted -and -not $_.IsSystemObject} | %{
$obj = New-Object -TypeName PSObject -Property @{
DatabaseName = $dbName
ObjectType = 'View'
ObjectName = $_.Name
}
$objects += $obj;
}
$_.UserDefinedFunctions | where{$_.IsEncrypted -and -not $_.IsSystemObject} | %{
$obj = New-Object -TypeName PSObject -Property @{
DatabaseName = $dbName
ObjectType = 'UDF'
ObjectName = $_.Name
}
$objects += $obj;
}
$_.Triggers | where{$_.IsEncrypted -and -not $_.IsSystemObject} | %{
$obj = New-Object -TypeName PSObject -Property @{
DatabaseName = $dbName
ObjectType = 'Trigger'
ObjectName = $_.Name
}
$objects += $obj;
}
}
$objects | SELECT DatabaseName, ObjectType, ObjectName
Leave a comment