SQL Server Logins And Users Audit (資料庫帳號權限清查)
2025-06-13
筆記如何定期進行資料庫帳號與登入權限清查的 Script。
伺服器 Logins Check
SELECT
name,
loginname,
CASE
WHEN sysadmin = 1
OR securityadmin = 1
OR serveradmin = 1
OR setupadmin = 1
OR processadmin = 1
OR diskadmin = 1
OR dbcreator = 1
OR bulkadmin = 1
THEN 1
ELSE 0
END AS special_role,
createdate,
updatedate
FROM
sys.syslogins
WHERE
status = 9 AND
denylogin = 0;資料庫 Users Check
IF OBJECT_ID('tempdb..#AllDatabaseUsersAndRolesWithLogin') IS NOT NULL
DROP TABLE #AllDatabaseUsersAndRolesWithLogin;
CREATE TABLE #AllDatabaseUsersAndRolesWithLogin (
db_name NVARCHAR(128),
user_name NVARCHAR(128),
login_name NVARCHAR(128), -- 新增登入名稱欄位
role_name NVARCHAR(128),
createdate DATETIME,
updatedate DATETIME,
islogin NVARCHAR(3)
);
DECLARE @sql NVARCHAR(MAX);
SET @sql = '
USE [?];
INSERT INTO #AllDatabaseUsersAndRolesWithLogin (db_name, user_name, login_name, role_name, createdate, updatedate, islogin)
SELECT
DB_NAME() AS db_name,
dp.name AS user_name,
sp.name AS login_name, -- 從 sys.server_principals 取得登入名稱
dr.name AS role_name,
dp.create_date AS createdate,
dp.modify_date AS updatedate,
CASE
WHEN dp.type IN (''S'', ''U'', ''G'') THEN ''Yes'' -- S: SQL User, U: Windows User, G: Windows Group
ELSE ''No''
END AS islogin
FROM
sys.database_principals AS dp
LEFT JOIN
sys.database_role_members AS drm ON dp.principal_id = drm.member_principal_id
LEFT JOIN
sys.database_principals AS dr ON drm.role_principal_id = dr.principal_id
LEFT JOIN
sys.server_principals AS sp ON dp.sid = sp.sid -- 根據 SID 聯結資料庫使用者與伺服器登入
WHERE
dp.type IN (''S'', ''U'', ''G'') -- S: SQL User, U: Windows User, G: Windows Group
AND dp.name NOT IN (''guest'', ''INFORMATION_SCHEMA'', ''sys''); -- 排除系統使用者
';
EXEC sp_MSforeachdb @command1 = @sql;
SELECT
db_name,
user_name,
ISNULL(login_name, 'N/A') AS login_name, -- 如果沒有對應的登入，顯示 'N/A'
ISNULL(role_name, 'No Role') AS role_name,
createdate,
updatedate,
islogin
FROM
#AllDatabaseUsersAndRolesWithLogin
ORDER BY
db_name,
user_name,
role_name;
DROP TABLE #AllDatabaseUsersAndRolesWithLogin;排程檢查 Server Logins 異動
週期性執行，所有的登入帳號會儲存在 LoginsHistory 資料表中累積上去，差異的部份則透過 Script 比較後寫入 LoginAuditLog 資料表。
LoginsHistory 資料表
-- 如果舊的資料表存在，請先刪除
IF OBJECT_ID('dbo.LoginsHistory', 'U') IS NOT NULL
DROP TABLE dbo.LoginsHistory;
GO
-- 根據 sys.syslogins 的欄位重建資料表
CREATE TABLE dbo.LoginsHistory (
[CaptureDate] DATETIME NOT NULL,
-- sys.syslogins columns
[sid] VARBINARY(85) NOT NULL,
[status] SMALLINT NULL,
[createdate] DATETIME NOT NULL,
[updatedate] DATETIME NOT NULL,
[accdate] DATETIME NOT NULL,
[name] SYSNAME NOT NULL,
[dbname] SYSNAME NOT NULL,
[language] SYSNAME NULL,
[denylogin] INT NOT NULL,
[hasaccess] INT NOT NULL,
[isntname] INT NOT NULL,
[isntgroup] INT NOT NULL,
[isntuser] INT NOT NULL,
[sysadmin] INT NOT NULL,
[securityadmin] INT NOT NULL,
[serveradmin] INT NOT NULL,
[setupadmin] INT NOT NULL,
[processadmin] INT NOT NULL,
[diskadmin] INT NOT NULL,
[dbcreator] INT NOT NULL,
[bulkadmin] INT NOT NULL,
[loginname] SYSNAME NOT NULL,
-- Custom columns
[RowChecksum] INT NULL
);
GO
-- 建立索引以提升效能
CREATE CLUSTERED INDEX IX_LoginsHistory_CaptureDate_Name
ON dbo.LoginsHistory (CaptureDate, name);
GOAuditLog 資料表
-- 建立新的 LoginAuditLog 資料表
IF OBJECT_ID('dbo.LoginAuditLog', 'U') IS NULL
BEGIN
CREATE TABLE dbo.LoginAuditLog (
[AuditLogID] INT IDENTITY(1,1) PRIMARY KEY,
[LogDate] DATETIME NOT NULL DEFAULT GETDATE(),
[EventType] NVARCHAR(20) NOT NULL, -- '新增', '刪除', '變更'
[LoginName] SYSNAME NOT NULL,
[ChangeDescription] NVARCHAR(MAX) NULL -- 記錄變更的詳細內容
);
END
GO作業 Scripts
/*******************************************************************
每月 SQL Server Logins 變更稽核作業 (v2.1 - 增加權限變更檢查)
1. 比對差異
2. 將新增、刪除、變更事件寫入 LoginsAuditLog 資料表
3. 封存本次 Logins 狀態
*******************************************************************/
SET NOCOUNT ON;
-- 變數宣告
DECLARE @CurrentCaptureDate DATETIME = GETDATE();
DECLARE @PreviousCaptureDate DATETIME;
-- 1. 將當前 logins 狀態存入暫存表
SELECT
l.*,
CHECKSUM(
l.status, l.updatedate, l.dbname, l.language, l.denylogin, l.hasaccess,
l.sysadmin, l.securityadmin, l.serveradmin, l.setupadmin, l.processadmin,
l.diskadmin, l.dbcreator, l.bulkadmin
) AS RowChecksum
INTO #CurrentLogins
FROM sys.syslogins AS l;
-- 2. 取得上一次快照的日期
SELECT TOP 1 @PreviousCaptureDate = CaptureDate
FROM dbo.LoginsHistory
ORDER BY CaptureDate DESC;
-- 3. 如果有舊資料才進行比對並記錄
IF @PreviousCaptureDate IS NOT NULL
BEGIN
-- 找出上次的資料
SELECT *
INTO #PreviousLogins
FROM dbo.LoginsHistory
WHERE CaptureDate = @PreviousCaptureDate;
-- >> 事件: 新增的登入 (Logins Added)
INSERT INTO dbo.LoginAuditLog (LogDate, EventType, LoginName, ChangeDescription)
SELECT
@CurrentCaptureDate,
'新增',
cur.name,
'LoginName: ' + cur.loginname + ', Default DB: ' + cur.dbname + ', Has Access: ' + CAST(cur.hasaccess AS VARCHAR)
FROM #CurrentLogins cur
WHERE cur.name NOT IN (SELECT name FROM #PreviousLogins);
-- >> 事件: 已刪除的登入 (Logins Deleted)
INSERT INTO dbo.LoginAuditLog (LogDate, EventType, LoginName, ChangeDescription)
SELECT
@CurrentCaptureDate,
'刪除',
prev.name,
'LoginName: ' + prev.loginname + ', Default DB: ' + prev.dbname
FROM #PreviousLogins prev
WHERE prev.name NOT IN (SELECT name FROM #CurrentLogins);
-- >> 事件: 已變更的登入 (Logins Modified)
INSERT INTO dbo.LoginAuditLog (LogDate, EventType, LoginName, ChangeDescription)
SELECT
@CurrentCaptureDate,
--- *** 1. 新增檢查權限變更的邏輯，用以決定事件類型 *** ---
CASE
WHEN cur.sysadmin <> prev.sysadmin OR
cur.securityadmin <> prev.securityadmin OR
cur.serveradmin <> prev.serveradmin OR
cur.setupadmin <> prev.setupadmin OR
cur.processadmin <> prev.processadmin OR
cur.diskadmin <> prev.diskadmin OR
cur.dbcreator <> prev.dbcreator OR
cur.bulkadmin <> prev.bulkadmin OR
cur.denylogin <> prev.denylogin OR
cur.hasaccess <> prev.hasaccess
THEN '高權限變更'
ELSE '一般變更'
END AS EventType,
cur.name,
--- *** 2. 新增檢查權限變更的邏輯，用以加上警告訊息 *** ---
CONCAT(
CASE
WHEN cur.sysadmin <> prev.sysadmin OR
cur.securityadmin <> prev.securityadmin OR
cur.serveradmin <> prev.serveradmin OR
cur.setupadmin <> prev.setupadmin OR
cur.processadmin <> prev.processadmin OR
cur.diskadmin <> prev.diskadmin OR
cur.dbcreator <> prev.dbcreator OR
cur.bulkadmin <> prev.bulkadmin
THEN '***重要權限變更!*** '
ELSE ''
END,
CONCAT_WS(
'; ',
CASE WHEN cur.dbname <> prev.dbname THEN 'dbname: ' + prev.dbname + ' -> ' + cur.dbname ELSE NULL END,
CASE WHEN cur.language <> prev.language THEN 'language: ' + ISNULL(prev.language, 'NULL') + ' -> ' + ISNULL(cur.language, 'NULL') ELSE NULL END,
CASE WHEN cur.denylogin <> prev.denylogin THEN 'denylogin: ' + CAST(prev.denylogin AS VARCHAR) + ' -> ' + CAST(cur.denylogin AS VARCHAR) ELSE NULL END,
CASE WHEN cur.hasaccess <> prev.hasaccess THEN 'hasaccess: ' + CAST(prev.hasaccess AS VARCHAR) + ' -> ' + CAST(cur.hasaccess AS VARCHAR) ELSE NULL END,
CASE WHEN cur.sysadmin <> prev.sysadmin THEN 'sysadmin: ' + CAST(prev.sysadmin AS VARCHAR) + ' -> ' + CAST(cur.sysadmin AS VARCHAR) ELSE NULL END,
CASE WHEN cur.securityadmin <> prev.securityadmin THEN 'securityadmin: ' + CAST(prev.securityadmin AS VARCHAR) + ' -> ' + CAST(cur.securityadmin AS VARCHAR) ELSE NULL END,
CASE WHEN cur.serveradmin <> prev.serveradmin THEN 'serveradmin: ' + CAST(prev.serveradmin AS VARCHAR) + ' -> ' + CAST(cur.serveradmin AS VARCHAR) ELSE NULL END,
CASE WHEN cur.setupadmin <> prev.setupadmin THEN 'setupadmin: ' + CAST(prev.setupadmin AS VARCHAR) + ' -> ' + CAST(cur.setupadmin AS VARCHAR) ELSE NULL END,
CASE WHEN cur.processadmin <> prev.processadmin THEN 'processadmin: ' + CAST(prev.processadmin AS VARCHAR) + ' -> ' + CAST(cur.processadmin AS VARCHAR) ELSE NULL END,
CASE WHEN cur.diskadmin <> prev.diskadmin THEN 'diskadmin: ' + CAST(prev.diskadmin AS VARCHAR) + ' -> ' + CAST(cur.diskadmin AS VARCHAR) ELSE NULL END,
CASE WHEN cur.dbcreator <> prev.dbcreator THEN 'dbcreator: ' + CAST(prev.dbcreator AS VARCHAR) + ' -> ' + CAST(cur.dbcreator AS VARCHAR) ELSE NULL END,
CASE WHEN cur.bulkadmin <> prev.bulkadmin THEN 'bulkadmin: ' + CAST(prev.bulkadmin AS VARCHAR) + ' -> ' + CAST(cur.bulkadmin AS VARCHAR) ELSE NULL END,
'updatedate: ' + CONVERT(VARCHAR, prev.updatedate, 120) + ' -> ' + CONVERT(VARCHAR, cur.updatedate, 120)
)
) AS ChangeDescription
FROM #CurrentLogins cur
JOIN #PreviousLogins prev ON cur.name = prev.name
WHERE cur.RowChecksum <> prev.RowChecksum;
-- 清理暫存表
DROP TABLE #PreviousLogins;
END
-- 4. 將當前快照存入歷史資料表
INSERT INTO dbo.LoginsHistory
SELECT
@CurrentCaptureDate,
l.sid, l.status, l.createdate, l.updatedate, l.accdate, l.name, l.dbname, l.language,
l.denylogin, l.hasaccess, l.isntname, l.isntgroup, l.isntuser, l.sysadmin,
l.securityadmin, l.serveradmin, l.setupadmin, l.processadmin, l.diskadmin,
l.dbcreator, l.bulkadmin, l.loginname, l.RowChecksum
FROM #CurrentLogins AS l;
-- 清理暫存表
DROP TABLE #CurrentLogins;