SQL Server 帳號清查作業指南

2026-08-12

關於定期執行資料庫伺服器帳號清查的相關作業指南與指令。

logo

說明

  • 資料庫當中的孤立使用者
  • 各資料庫的使用者
  • 資料庫伺服器層的登入 (login)

檢查孤立使用者的方式，會一併產生刪除的作業指令

-- 建立暫存資料表來儲存所有資料庫的結果
IF OBJECT_ID('tempdb..#OrphanedUsers') IS NOT NULL
    DROP TABLE #OrphanedUsers;

CREATE TABLE #OrphanedUsers (
    DatabaseName    SYSNAME,
    UserName        SYSNAME,
    UserSID         VARBINARY(85),
    OwnsObjects     BIT,          -- 1 表示擁有物件，0 表示未擁有物件
    OwnedObjects    NVARCHAR(MAX),-- 擁有物件的名稱清單
    DropUserTSQL    NVARCHAR(MAX) -- 自動產生的清除指令
);

-- 使用 dynamic SQL 來走訪每個資料庫
DECLARE @SQL NVARCHAR(MAX) = '
USE [?];

-- 排除系統資料庫與唯讀/離線的資料庫
IF DB_ID() > 4 AND HAS_DBACCESS(DB_NAME()) = 1 AND DATABASEPROPERTYEX(DB_NAME(), ''Status'') = ''ONLINE''
BEGIN
    INSERT INTO #OrphanedUsers (DatabaseName, UserName, UserSID, OwnsObjects, OwnedObjects, DropUserTSQL)
    SELECT 
        DB_NAME() AS DatabaseName,
        dp.name AS UserName,
        dp.sid AS UserSID,
        CASE WHEN COUNT(o.object_id) > 0 THEN 1 ELSE 0 END AS OwnsObjects,
        ISNULL(STRING_AGG(CAST(o.name AS NVARCHAR(MAX)), '', ''), ''(無)'') AS OwnedObjects,
        -- 判斷是否擁有物件，產生對應的指令 (移交權限 -> 刪除 User)
        ISNULL(
            STRING_AGG(
                CAST(''ALTER AUTHORIZATION ON OBJECT::['' + SCHEMA_NAME(o.schema_id) + ''].['' + o.name + ''] TO [dbo];'' AS NVARCHAR(MAX)), 
                '' ''
            ), 
            ''''
        ) + '' DROP USER ['' + dp.name + ''];'' AS DropUserTSQL
    FROM sys.database_principals dp
    -- 尋找對應 Login 不存在的 SQL User (type = ''S'')
    LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
    -- 關聯該 User 所擁有的物件
    LEFT JOIN sys.objects o ON dp.principal_id = o.principal_id
    WHERE dp.type = ''S''                       -- 僅針對 SQL 使用者
      AND sp.sid IS NULL                      -- 伺服器層級找不到對應 Login
      AND dp.authentication_type_desc = ''INSTANCE'' -- 排查資料庫內建角色/帳號
      AND dp.principal_id > 4                 -- 排除 dbo, guest, INFORMATION_SCHEMA, sys
    GROUP BY dp.name, dp.sid;
END
';

-- 執行跨資料庫查詢
EXEC sp_MSforeachdb @SQL;

-- 顯示查詢結果
SELECT 
    DatabaseName AS [資料庫名稱],
    UserName     AS [孤立使用者名稱],
    CASE OwnsObjects 
        WHEN 1 THEN N'是 (Yes)' 
        ELSE N'否 (No)' 
    END          AS [是否擁有物件],
    OwnedObjects AS [擁有的物件清單],
    -- 結合切換資料庫的語法，產出一整串可直接複製執行的完整 T-SQL
    'USE [' + DatabaseName + ']; ' + DropUserTSQL AS [移除 User 的 T-SQL]
FROM #OrphanedUsers
ORDER BY DatabaseName, UserName;

-- 清除暫存資料表
DROP TABLE #OrphanedUsers;

如果使用者擁有物件或角色的權限，必須先將物件與角色的擁有者讓與其他使用者才行刪除。

USE [DBNAME]
GO
ALTER AUTHORIZATION ON SCHEMA::[USERNAME] TO [dbo]
GO
#Database #SQLServer