SQL Server 2005 SysAdmin 登录审计的详细介绍

以下的文章主要向大家描述的是SQL Server 2005 SysAdmin 登录审计，假如是以sysadmin角色的成员在SQL Server数据库中出现时，我都有审计这一过程的想法。审计所有成功的登录可以提供信息，然而，它记录每一次连接。

Sysadmin登录在一片喧闹声中正失去它自己的地位。我该怎样做才能只通过SQL Server 2005 sysadmin登

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

专家解答

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

为了审计SQL Server 2005 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从句，我们可以测试引入的登录是否是SQL Server 2005 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   

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

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   

以上的相关内容就是对SQL Server 2005 SysAdmin登录审计的介绍，望你能有所收获。