Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server
Article

Generating SQL Server CLR Assemblies report

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
10 Jun 2012CPOL6 min read 20.2K   192   5  
PowerShell script that generates an HTML report displaying different information about the SQL Server databases, their CLR assemblies and owners.

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): 

Image 1

Image 2

Image 3 

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: 

 Image 4

 Image 5

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: 

Image 6

Image 7

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):

C#
param
(
	[Parameter(Mandatory=$true)]
  	[string]$server	
) 
Then the SMO library must be loaded:
C#
# Load-SMO assembly
[Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') 

And a server object representing the server instance is created:

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

Now we have all the SQL server information available for us through this $serverInstance object.  Loop through all servers’ databases and get their information: 

C#
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. 

All the pieces of the HTML converted information are concatenated and fed to the Cmdlet ConvertTo-Html with a definition of the HTML page style. This Cmdlet write the full page to a file named SQLServerCLrAssemblies.htm. This file is located under C:\Documents and Settings\<user>\My documents. Finally it displays the generated file on the browser. 

Running the script 

 In order to run the script:

  • 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. 

Image 8

Interpretation of the generated report 

In this section we will explain the some pieces of the information displayed in the generated report.

Database and its assemblies informationImage 9

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:

It displays the registered modules (stored procedures, functions…) of an assembly and the assemblies that are referenced in it. For example, we notice that the assembly SampleLibrary has two modules: SampleSP which is a stored procedure and GetBookCount which is a function. This assembly references two assemblies: Tools and MyMessages assemblies.

Assemblies Owner information

Image 10

  • 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  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
France France
Software Architect, Consultant and Solution Initiator with over 20 years of industry experience designing and developing software in different domains: semiconductor manufacturing, electrical energy distribution, solar energy production, medical device manufacturing, PC manufacturing etc.

Comments and Discussions

 
-- There are no messages in this forum --