SQL Server 2005 SysAdmin登录审计

核心提示：无论何时以sysadmin角色的成员登录到我的SQL Server中，我都想审计这一过程，SQL Server 2005 SysAdmin登录审计，审计所有成功的登录可以提供信息，然而，这里有一个系统功能ORIGINAL_LOGIN()，它提供了相关的信息，它记录每一次连接，Sysadmin登录在一片喧闹声中正失去它自

无论何时以sysadmin角色的成员登录到我的SQL Server中，我都想审计这一过程。审计所有成功的登录可以提供信息，然而，它记录每一次连接。Sysadmin登录在一片喧闹声中正失去它自己的地位。我该怎样做才能只通过sysadmin登录到我的SQL Server实例呢?

专家解答

在SQL Server 2005 Service Pack 2中，微软把登录触发器引入到核心功能中。与DDL和DML触发器一样，这些触发器在特定的事件上被激活，在这个例子中，无论何时登录到SQL Server实例中，它都会被激活。当某个特定角色的成员登录时，比如syadmin固定服务器角色，我们可以使用一个登录触发器来审计。

为了审计sysadmin固定服务器角色的成员，我们需要使用两个系统视图：sys.server_role_members 和 sys.server_principals。当登录事件发生时，我们将连接这两个视图来确定这个登录是不是sysadmin角色的成员。我们可通过如下连接两个视图来得到这些成员：

　　　SELECT sp.principal_id
　　FROM sys.server_role_members srm
　　INNER JOIN sys.server_principals sp
　　ON srm.member_principal_id = sp.principal_id
　　WHERE srm.role_principal_id = (
　　SELECT principal_id
　　FROM sys.server_principals
　　WHERE [Name] = 'sysadmin')

这个查询将是我们登录触发器的基础。通过在我们代码的最后一部分增加一个AND从句，我们可以测试引入的登录是否是sysadmin固定服务器角色的成员。

当事件发生时，我们也需要一些空间来记录这些事件。其中最简单的方法是在一个工作数据库中使用一张表来达到这个目的。考虑到这些例子的目的性，我将假设这张表能够以DBAWork名义存储在一个DBA数据库中。以下是相应的代码：

　　　CREATE TABLE dbo.AuditSysAdminLogin
　　(AuditEventId INT IDENTITY(1,1) NOT NULL,
　　EventTime DATETIME NOT NULL,
　　ServerLogin NVARCHAR(100) NOT NULL,
　　CONSTRAINT PK_AuditSysAdminLogin PRIMARY KEY CLUSTERED (AuditEventID));
　　GO

一旦我们有了审计表，我们就可以创建自己的登录触发器。登录触发器的基本句法和DDL触发器的句法相类似，如下显示： 　　　　　CREATE TRIGGER
　　ON ALL SERVER
　　FOR LOGON
　　AS

按照这种格式和使用以上确定的查询来确定哪一个是SysAdmin角色的成员，那么我们缺少的唯一方面是确认登录的一种方式。这里有一个系统功能ORIGINAL_LOGIN()，它提供了相关的信息，现在把它放在一起，那么下面就是我们的登录触发器：

　　　USE master;
　　GO
　　CREATE TRIGGER trigLogon_CheckForSysAdmin
　　ON ALL SERVER
　　FOR LOGON
　　AS
　　BEGIN
　　IF EXISTS (
　　SELECT sp.principal_id
　　FROM sys.server_role_members srm
　　JOIN sys.server_principals sp
　　ON srm.member_principal_id = sp.principal_id
　　WHERE role_principal_id = (
　　SELECT principal_id
　　FROM sys.server_principals
　　WHERE NAME = 'sysadmin')
　　AND ORIGINAL_LOGIN() = sp.NAME)
　　BEGIN
　　INSERT INTO DBAWork.dbo.AuditSysAdminLogin
　　(EventTime, ServerLogin)
　　VALUES
　　(GETDATE(), ORIGINAL_LOGIN())
　　END;
　　END;
　　GO