Generating SQL Server CLR Assemblies report

• Download SQLCLrAssemblies Script - 2.3 KB

Introduction  

In real situations we have so many databases in our SQL server and some of them may have one or more registered assemblies. Some of these assemblies may reference other assemblies. And we may have different versions of the same assembly in different databases. It will be nice if we have a script that reports all necessary information about all assemblies in all databases. So, the purpose of this article is to create this script. 

Let us see some pieces of the report generated by this article’s script (it’s an HTML page): 

The interpretation of this report will be done later in this article. 

Elements used in the script creation<o:p> 

This script is created using these tools/libraries: 

1. Scripting
• PowerShell
• Script Editor (optional)
2. Retrieving information: 
• SQL Server Management Objects (SMO).
• CLR Catalog views.
• System stored procedure sp_helpuser. 

I used a mix of SMO, CLR Catalog Views and sp_helpuser methods to retrieve assemblies’ information because either the information that I search for cannot be retrieved using one method but it can be retrieved using another one or it’s faster to get the information using one method than another. 

Scripting 

PowerShell

It’s the command-line/scripting environment from Microsoft. Built on the Microsoft .NET Framework, It provides a command line environment and a scripting language. Our script is written with this language. If not already installed in your system, you can get it from this site  http://support.microsoft.com/kb/968929 (normally it’s already installed if you have SQL Server 2008 or above installed).

Script Editor (optional) 

A notepad is sufficient to create/edit the script. But to have other functionalities that facilitate scripting like: IntelliSense, Code completion, instantly syntax checking etc. it’s better to have a good PowerShell IDE. The installation of PowerShell installs also an IDE found at start menu/Accessories/Windows PowerShell/ Windows PowerShell ISE. I used PowerGui Script Editor which is a very good PowerShell script editor that can be downloaded for free from http://powergui.org. 

Retrieving Information 

SQL Server Management Objects (SMO)<o:p> 

It’s a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. It allows us to do things that SQL Server Management studio application can do.

If not already installed in your system, then: 

1. Download and install SQL Server System CLR Types which is required by SQL Server Management Objects: you can download it from  http://www.microsoft.com/download/en/details.aspx?id=29065  (search in the same page “Microsoft® System CLR Types for Microsoft® SQL Server® 2012″ and choose either x86 or x64 package with respect to your system).
2. Download and install SQL Server Management Objects from  http://www.microsoft.com/download/en/details.aspx?id=29065  (search in the same page “Microsoft® SQL Server® 2012 Shared Management Objects” and choose either x86 or x64 package with respect to your system). 

SMO 2012 is compatible with SQL Server 2005 and later versions.  

CLR Catalog Views<o:p> 

They are a set of views enabling us to retrieve information about the registered assemblies in the current database. They can be visualized in each database under System Views node in SQL Server Management studio: 

System stored procedure sp_helpuser  

It’s a stored procedure that reports information about SQL Serve users, roles and login in the current database. It’s located in each database under System Stored Procedures node in SQL Server Management studio: 

The Script   

The script defines the unique mandatory parameter (the server name) that must be given in the script command-line (you can download the full script from the link at the top of this page):

param
(
	[Parameter(Mandatory=$true)]
  	[string]$server	
) 

# Load-SMO assembly
[Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') 

# Get the server-object 
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server

foreach ($database in $serverInstance.Databases)
{
  $htmlServerInfo += GetDatabaseInfo $database		
  $htmlServerInfo += GetAssembliesInfo $database
  $htmlServerInfo += GetAssembliesModulesInfo $database
  foreach ($assembly in $database.Assemblies) {				
  if(-not $users.ContainsKey($assembly.Owner))
  {				
    $userDataset = $database.ExecuteWithResults('sp_helpuser')	
    $userRow = $userDataset.Tables[0].Rows | where {$_.UserName -eq $assembly.Owner}				
    $login = $serverInstance.Logins[$userRow.LoginName]
    $user = New-Object -TypeName psobject
    $users[$assembly.Owner]= `
    $user  | Add-Member -MemberType NoteProperty –Name Name –Value $assembly.Owner –PassThru `
   | Add-Member -MemberType NoteProperty –Name LoginName –Value $userRow.LoginName –PassThru `
   | Add-Member -MemberType NoteProperty –Name LoginType –Value $login.LoginType –PassThru `
   | Add-Member -MemberType NoteProperty –Name AsymmetricKey –Value $login.AsymmetricKey –PassThru			
   }			
   }
   $htmlServerInfo += '<br/><br/>'
}	
$htmlServerInfo += GetUserInfo $users 

The above script snippet:

• Fetches the following information for each database:

• Database information (GetDatabaseInfo function)
• Database assemblies’ information (GetAssembliesInfo function)
• Database assemblies’ modules information (GetAssembliesModulesInfo function)

Each one of these functions converts its information to HTML table through the Cmdlet ConvertTo-Html. We used the –Fragment parameter with this Cmdlet in order to generate only HTML tables. The HTML, HEAD, TITLE and BODY tags are omitted.

• Fetches the owner, its login and the login type of each assembly. Then it formats this information and converts it to HTML table using the GetUserInfo function. 

• Open the PowerShell console window: Start menu/All programs/Accessories/Windows PowerShell/Windows PowerShell. 
• If the script execution does not enabled in PowerShell , then enable it by typing the following command in the console: 

Set-ExecutionPolicy RemoteSigned  

• Type the full path to the script file with the mandatory parameter –server which is the SQL Server name, and then Enter.  class names, the methods and properties, any tricks or tips. 

Interpretation of the generated report 

Database and its assemblies information

First table: 

• SampleDB: the database name.
• Trustworthy: False. 

It means that the instance of SQL Server does not trust the database and the contents within it. Normally this property is used (set to ON) to run UNSAFE or EXTERNAL permission assemblies in SQL Server. It‘s one of two methods to run these types of assemblies. The other method is signing the assembly and setting an owner having the right permissions as the case in the assembly SampleLibrary. Microsoft does not recommend setting Trustworthy to ON. 

Second table:  

• Version: the assembly’s version.
• Signed: whether the assembly is signed with a strong name or not.
• Create Date: the assembly creation date.
• Is visible: 
• Yes:  the assembly has registered modules (stored procedures, functions…)
• No: the assembly is used by other assemblies in the database (referenced assembly)
• Security Level: The registered permission of the assembly (SAFE, EXTERNAL or UNSAFE (unrestricted)).
• Owner: The owner of the assembly. 

Third table:

Assemblies Owner information

• User: the name of the owner of an assembly 
• Login: the login name of the owner.
• Login Type: the login type of the login. For example the SampleLibraryLogin has an AsymmetricKey login type. This login has been created using the asymmetric key named SampleLibraryKey.
• Asymmetric Key: The asymmetric key name. For example the asymmetric key SampleLibraryKey has been created using the public key of the assembly SampleLibrary.

Conclusion 

This article’ PowerShell script generates an HTML report displaying different information about the SQL Server databases, their CLR assemblies and owners. It uses different methods for retrieving data from SQL Server like SQL Server Management Objects (SMO), CLR Catalog views and sp_helpuser stored procedure. 

History  

• Jun 10, 2012 - Initial Version