开发学院数据库MSSQL Server SQL Server 2005 SysAdmin登录审计 阅读

SQL Server 2005 SysAdmin登录审计

 2008-11-19 10:09:55 来源:WEB开发网   
核心提示:无论何时以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

Tags:SQL Server SysAdmin

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接