65.9K
CodeProject 正在变化。 阅读更多。
Home

生成 SQL Server CLR 程序集报告

Manar Ezzadeen
emptyStarIconemptyStarIconemptyStarIconemptyStarIconemptyStarIcon

0/5 (0投票)

2012年6月10日

CPOL

6分钟阅读

viewsIcon

21369

downloadIcon

225

PowerShell 脚本，用于生成 HTML 报告，显示有关 SQL Server 数据库、其 CLR 程序集和所有者的各种信息。

简介  

在实际情况中，我们的 SQL Server 中有许多数据库，其中一些可能有一个或多个已注册的程序集。其中一些程序集可能引用其他程序集。并且我们可能在不同的数据库中有相同程序集的不同版本。如果我们有一个脚本可以报告所有数据库中所有程序集的必要信息，那就很好了。因此，本文的目的是创建这个脚本。

让我们看一下本文脚本生成的报告的一些片段（这是一个 HTML 页面）：

 

本文将稍后解释此报告。

脚本创建中使用的元素 

此脚本是使用以下工具/库创建的：

  1. 脚本
    • PowerShell
    • 脚本编辑器（可选）
  2. 检索信息：
    • SQL Server Management Objects (SMO)。
    • CLR 目录视图。
    • 系统存储过程 sp_helpuser。

我使用了 SMO、CLR 目录视图和 sp_helpuser 方法的混合来检索程序集信息，因为我搜索的信息要么无法通过一种方法检索，但可以通过另一种方法检索，要么通过一种方法获取信息比另一种方法更快。

脚本编写

PowerShell

这是 Microsoft 的命令行/脚本环境。它建立在 Microsoft .NET Framework 之上，提供了一个命令行环境和一种脚本语言。我们的脚本是用这种语言编写的。如果您的系统尚未安装，您可以从以下网站获取： http://support.microsoft.com/kb/968929 （如果您的 SQL Server 2008 或更高版本已安装，通常它已安装）。

脚本编辑器（可选）

记事本足以创建/编辑脚本。但是，要获得其他有助于脚本编写的功能，例如：IntelliSense、代码补全、即时语法检查等，最好有一个好的 PowerShell IDE。PowerShell 的安装也会安装一个 IDE，该 IDE 可以在开始菜单/附件/Windows PowerShell/Windows PowerShell ISE 中找到。我使用了 PowerGui Script Editor，它是一个非常好的 PowerShell 脚本编辑器，可以从 http://powergui.org 免费下载。


检索信息

SQL Server Management Objects (SMO)

它是一组用于编程管理 Microsoft SQL Server 各个方面的对象。它允许我们做 SQL Server Management Studio 应用程序可以做的事情。

如果您的系统尚未安装，请执行以下操作：

  1. 下载并安装 SQL Server System CLR Types，这是 SQL Server Management Objects 所必需的：您可以从 http://www.microsoft.com/download/en/details.aspx?id=29065  下载（在同一页面搜索“Microsoft® System CLR Types for Microsoft® SQL Server® 2012”，然后根据您的系统选择 x86 或 x64 程序包）。
  2. 下载并安装 SQL Server Management Objects：从 http://www.microsoft.com/download/en/details.aspx?id=29065  （在同一页面搜索“Microsoft® SQL Server® 2012 Shared Management Objects”，然后根据您的系统选择 x86 或 x64 程序包）。

SMO 2012 与 SQL Server 2005 及更高版本兼容。

CLR 目录视图 

它们是一组视图，使我们能够检索有关当前数据库中已注册程序集的信息。它们可以在 SQL Server Management Studio 中每个数据库的系统视图节点下找到：

 

 

系统存储过程 sp_helpuser  

这是一个报告有关当前数据库中的 SQL Server 用户、角色和登录信息的存储过程。它位于 SQL Server Management Studio 中每个数据库的系统存储过程节点下：

脚本

该脚本定义了脚本命令行中必须提供的唯一强制参数（服务器名称）（您可以从页面顶部的链接下载完整脚本）

param
(
	[Parameter(Mandatory=$true)]
  	[string]$server	
)
然后必须加载 SMO 库
# Load-SMO assembly
[Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

并且创建了一个代表服务器实例的服务器对象

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

现在，通过这个$serverInstance 对象，我们可以获取所有 SQL 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

上面的脚本片段

  • 为每个数据库提取以下信息
    • 数据库信息（GetDatabaseInfo 函数）
    • 数据库程序集信息（GetAssembliesInfo 函数）
    • 数据库程序集模块信息（GetAssembliesModulesInfo 函数）
这些函数中的每一个都通过 Cmdlet ConvertTo-Html 将其信息转换为 HTML 表。我们使用此 Cmdlet 的–Fragment 参数来仅生成 HTML 表。HTML、HEAD、TITLE 和 BODY 标签被省略。
  • 提取每个程序集的拥有者、其登录名和登录类型。然后它使用 GetUserInfo 函数格式化此信息并将其转换为 HTML 表。

所有 HTML 转换信息的片段都被连接起来并馈送给 Cmdlet ConvertTo-Html，并定义了 HTML 页面的样式。此 Cmdlet 将完整页面写入名为SQLServerCLrAssemblies.htm 的文件。该文件位于C:\Documents and Settings\<user>\My documents 下。最后，它在浏览器中显示生成的 Ailes。

运行脚本

 要运行脚本

  • 打开 PowerShell 控制台窗口：开始菜单/所有程序/附件/Windows PowerShell/Windows PowerShell。
  • 如果 PowerShell 中未启用脚本执行，请在控制台中键入以下命令来启用它：
Set-ExecutionPolicy RemoteSigned  
  • 键入脚本文件的完整路径以及强制参数 –server，即 SQL Server 名称，然后按 Enter。 类名、方法和属性、任何技巧或窍门。

生成的报告的解释

在本节中，我们将解释生成报告中显示的一些信息。

数据库及其程序集信息

第一个表：

 

  • SampleDB：数据库名称。
  • Trustworthy：False。

这意味着 SQL Server 实例不信任数据库及其内部内容。通常，此属性用于（设置为 ON）在 SQL Server 中运行 UNSAFE 或 EXTERNAL 权限的程序集。它是运行这些类型程序集的两种方法之一。另一种方法是签名程序集并设置具有正确权限的拥有者，如程序集SampleLibrary 中的情况。Microsoft 不建议将 Trustworthy 设置为 ON。

第二个表：

  • Version：程序集的版本。
  • Signed：程序集是否已使用强名称签名。
  • Create Date：程序集的创建日期。
  • Is visible: 
    • Yes：程序集具有已注册的模块（存储过程、函数……）
    • No：程序集被数据库中的其他程序集使用（引用的程序集）
  • Security Level：程序集的已注册权限（SAFE、EXTERNAL 或 UNSAFE（无限制））。
  • Owner：程序集的拥有者。

第三个表

它显示了程序集的已注册模块（存储过程、函数……）以及它所引用的程序集。例如，我们注意到程序集SampleLibrary 有两个模块：SampleSP（一个存储过程）和GetBookCount（一个函数）。此程序集引用了两个程序集：Tools 和MyMessages 程序集。

程序集拥有者信息


  • User：程序集拥有者的名称。
  • Login：拥有者的登录名。
  • Login Type：登录的登录类型。例如，SampleLibraryLogin 具有AsymmetricKey 登录类型。此登录是使用名为SampleLibraryKey 的非对称密钥创建的。
  • Asymmetric Key：非对称密钥名称。例如，非对称密钥SampleLibraryKey 是使用程序集SampleLibrary 的公钥创建的。

结论 

本文的 PowerShell 脚本生成了一个 HTML 报告，其中显示了有关 SQL Server 数据库、其 CLR 程序集和拥有者的各种信息。它使用不同的方法从 SQL Server 检索数据，例如 SQL Server Management Objects (SMO)、CLR Catalog 视图和 sp_helpuser 存储过程。

历史  

  • 2012 年 6 月 10 日 - 初始版本

© . All rights reserved.