Get Encrypted objects

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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.