SQL Server Logins And Users Audit (資料庫帳號權限清查)

2024-05-20

筆記如何定期進行資料庫帳號與登入權限清查的 Script。

Balloon title

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @dbname NVARCHAR(128);

-- 創建臨時表來存儲結果
IF OBJECT_ID('tempdb..#UserDetails') IS NOT NULL DROP TABLE #UserDetails;

CREATE TABLE #UserDetails (
    dbname NVARCHAR(128),
    username NVARCHAR(128),
    user_type NVARCHAR(60),
    loginname NVARCHAR(128),
    permission_name NVARCHAR(128),
    permission_state NVARCHAR(60),
    is_login_active NVARCHAR(10)
);

-- 動態生成查詢語句並插入到臨時表
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 
        'USE [' + @dbname + '];' + CHAR(13) + CHAR(10) +
        'INSERT INTO #UserDetails (dbname, username, user_type, loginname, permission_name, permission_state, is_login_active) ' +
        'SELECT ''' + @dbname + ''' AS dbname, ' +
        'u.name AS username, ' +
        'u.type_desc AS user_type, ' +
        'l.name AS loginname, ' +
        'p.permission_name, ' +
        'p.state_desc AS permission_state, ' +
        'CASE WHEN l.is_disabled = 0 THEN ''Active'' ELSE ''Inactive'' END AS is_login_active ' +
        'FROM sys.database_principals u ' +
        'LEFT JOIN sys.server_principals l ON u.sid = l.sid ' +
        'LEFT JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id ' +
        'WHERE u.type IN (''S'', ''U'');' + CHAR(13) + CHAR(10);
    
    EXEC sp_executesql @sql;

    FETCH NEXT FROM db_cursor INTO @dbname;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- 顯示所有結果
SELECT * FROM #UserDetails;