無論何時(shí)以sysadmin角色的成員登錄到我的SQL Server中，我都想審計(jì)這一過程。審計(jì)所有成功的登錄可以提供信息，然而，它記錄每一次連接。Sysadmin登錄在一片喧鬧聲中正失去它自己的地位。我該怎樣做才能只通過sysadmin登錄到我的SQL Server實(shí)例呢?
專家解答
在SQL Server 2005 Service Pack 2中，微軟把登錄觸發(fā)器引入到核心功能中。與DDL和DML觸發(fā)器一樣，這些觸發(fā)器在特定的事件上被激活，在這個(gè)例子中，無論何時(shí)登錄到SQL Server實(shí)例中，它都會(huì)被激活。當(dāng)某個(gè)特定角色的成員登錄時(shí)，比如syadmin固定服務(wù)器角色，我們可以使用一個(gè)登錄觸發(fā)器來審計(jì)。
為了審計(jì)sysadmin固定服務(wù)器角色的成員，我們需要使用兩個(gè)系統(tǒng)視圖：sys.server_role_members 和 sys.server_principals。當(dāng)?shù)卿浭录l(fā)生時(shí)，我們將連接這兩個(gè)視圖來確定這個(gè)登錄是不是sysadmin角色的成員。我們可通過如下連接兩個(gè)視圖來得到這些成員：
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')
這個(gè)查詢將是我們登錄觸發(fā)器的基礎(chǔ)。通過在我們代碼的最后一部分增加一個(gè)AND從句，我們可以測(cè)試引入的登錄是否是sysadmin固定服務(wù)器角色的成員。
當(dāng)事件發(fā)生時(shí)，我們也需要一些空間來記錄這些事件。其中最簡單的方法是在一個(gè)工作數(shù)據(jù)庫中使用一張表來達(dá)到這個(gè)目的。考慮到這些例子的目的性，我將假設(shè)這張表能夠以DBAWork名義存儲(chǔ)在一個(gè)DBA數(shù)據(jù)庫中。以下是相應(yīng)的代碼：
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
一旦我們有了審計(jì)表，我們就可以創(chuàng)建自己的登錄觸發(fā)器。登錄觸發(fā)器的基本句法和DDL觸發(fā)器的句法相類似，如下顯示：
CREATE TRIGGER
ON ALL SERVER
FOR LOGON
AS
按照這種格式和使用以上確定的查詢來確定哪一個(gè)是SysAdmin角色的成員，那么我們?nèi)鄙俚姆矫媸谴_認(rèn)登錄的一種方式?？荚?大提示系統(tǒng)功能ORIGINAL_LOGIN()，它提供了相關(guān)的信息，現(xiàn)在把它放在一起，那么下面就是我們的登錄觸發(fā)器：
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 Service Pack 2中，微軟把登錄觸發(fā)器引入到核心功能中。與DDL和DML觸發(fā)器一樣，這些觸發(fā)器在特定的事件上被激活，在這個(gè)例子中，無論何時(shí)登錄到SQL Server實(shí)例中，它都會(huì)被激活。當(dāng)某個(gè)特定角色的成員登錄時(shí)，比如syadmin固定服務(wù)器角色，我們可以使用一個(gè)登錄觸發(fā)器來審計(jì)。
為了審計(jì)sysadmin固定服務(wù)器角色的成員，我們需要使用兩個(gè)系統(tǒng)視圖：sys.server_role_members 和 sys.server_principals。當(dāng)?shù)卿浭录l(fā)生時(shí)，我們將連接這兩個(gè)視圖來確定這個(gè)登錄是不是sysadmin角色的成員。我們可通過如下連接兩個(gè)視圖來得到這些成員：
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')
這個(gè)查詢將是我們登錄觸發(fā)器的基礎(chǔ)。通過在我們代碼的最后一部分增加一個(gè)AND從句，我們可以測(cè)試引入的登錄是否是sysadmin固定服務(wù)器角色的成員。
當(dāng)事件發(fā)生時(shí)，我們也需要一些空間來記錄這些事件。其中最簡單的方法是在一個(gè)工作數(shù)據(jù)庫中使用一張表來達(dá)到這個(gè)目的。考慮到這些例子的目的性，我將假設(shè)這張表能夠以DBAWork名義存儲(chǔ)在一個(gè)DBA數(shù)據(jù)庫中。以下是相應(yīng)的代碼：
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
一旦我們有了審計(jì)表，我們就可以創(chuàng)建自己的登錄觸發(fā)器。登錄觸發(fā)器的基本句法和DDL觸發(fā)器的句法相類似，如下顯示：
CREATE TRIGGER
ON ALL SERVER
FOR LOGON
AS
按照這種格式和使用以上確定的查詢來確定哪一個(gè)是SysAdmin角色的成員，那么我們?nèi)鄙俚姆矫媸谴_認(rèn)登錄的一種方式?？荚?大提示系統(tǒng)功能ORIGINAL_LOGIN()，它提供了相關(guān)的信息，現(xiàn)在把它放在一起，那么下面就是我們的登錄觸發(fā)器：
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