SQLServer2005SysAdmin登录审计

每当我作为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[/Br/]FROM sys . server _ role _ members SRM[/Br/]inner join sys . server _ principals sp[/Br/]ON SRM . member _ principal _ id = sp . principal _ id[/Br/]WHERE SRM . role _ principal _ id =([/Br/]SELECT principal _ id[/Br/]FROM sys . server _ principals[/Br/]WHERE[name]= ' sysadmin ')[/Br/]此查询将是我们登录触发器的基础。通过在代码的最后部分添加AND子句，我们可以测试引入的登录名是否是sysadmin固定服务器角色的成员。
当事件发生时，我们也需要一些空房间来记录这些事件。最简单的方法之一是使用工作数据库中的表来实现这个目标。考虑到这些例子的目的，我将假设这个表可以以DBAWork的名义存储在DBA数据库中。下面是对应的代码:
createtabledbo . AuditSysAdminLogin
(audit event id int identity(1，1) not null，
eventtime datetime not null，
server log in NVARCHAR(100)NOT NULL，
CONSTRAINT PK _ AuditSysAdminLogin主键CLUSTERED(audit event id))；
GO
一旦我们有了审计表单，我们就可以创建自己的登录触发器了。login trigger的基本语法与DDL trigger类似，如下所示:
在所有服务器
上创建trigger
for log in
as
按照这种格式并使用上面确定的查询来确定哪个是SysAdmin角色的成员，我们缺少的是一种确认登录的方式。考试。大提示系统函数ORIGINAL_LOGIN()，提供相关信息。现在放在一起，那么这里就是我们的登录触发器:
使用master
GO
CREATE trigglogon _ CheckForSysAdmin
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF existing
SELECT sp . principal _ id
FROM sys . SERVER _ role _ members SRM
JOIN sys . SERVER _ principal sp
ON SRM . member _ principal _ id = sp . principal _ id
其中role_principal_id = .NAME)
BEGIN
INSERT INTO dbawork . dbo . auditsysadminlogin
(event time，server log in)
VALUES
(GETDATE()，ORIGINAL _ log in())
END；
END；
走