SQL – Find encrypted objects using sql_modules or Powershell

sys.sql_modules catalog view which got introduced from SQL 2005 onwards. The sql_modules which includes the objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module.

 SQL

SELECT 
sm.object_id, 
OBJECT_NAME(sm.object_id) AS object_name, 
o.type, o.type_desc, 
sm.definition, 
CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted] 
FROM sys.sql_modules AS sm 
JOIN sys.objects AS o ON sm.object_id = o.object_id 
ORDER BY o.type;
sql_modules to list only stored procedures then you can join with sys.procedures
SQL
SELECT SP.*, 
CASE  
WHEN SM.definition IS NULL THEN 'Encyrpted' END AS IsEncrypted 
FROM sys.procedures AS SP     
LEFT JOIN sys.sql_modules AS SM    ON SP.object_id = SM.object_id 
 PowerShell :-

Change the Servername and DBName in the below code. Execute the below code from PowerShell-ISE

$server = "<ServerName>" 
$database = "<DBName>" 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null 
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server 
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database") 
 
# Get the database and Its objects 
$db = $srv.Databases[$database] 
$storedProcs = $db.StoredProcedures | Where-object {$_.IsEncrypted} 
$storedProcs|select name
 Download the code: –

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

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 )

Google+ photo

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

Connecting to %s