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()，它提供了相关的信息，现在把它放在一起，那么下面就是我们的登录触发器：

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登录审计的介绍，望你能有所收获。

【编辑推荐】

1. SQL Server数据库Processor Affinity概述
2. 破解SQL Server占内存过多很简单！
3. SQL Server 2005快照与查询的使用场景
4. SQL Server拆分字符串的3常用种方法
5. SQL Server2005插入汉字变问号的破解