DBUser에 대한 DBRole 부여 현황

설명

현재 데이터베이스에 대해서 Login하는 사용자별로 부여된 권한을 추출하고자 하는데 사용이 됩니다. Object 별로 주어지는 권한은 별도의 쿼리로 조회를 해야 합니다. 아래의 쿼리로 추출되는 결과는 DBRole을 부여받은 DB User에 대한 정보입니다.

 

DB에서 DBUser에 대해 부여된 권한만 조회할 경우 Server 기준의 Login과 DB User간 매핑 정보가 누락되기 때문에 Login이 가능하지 않은 사용자도 권한이 부여된 것처럼 보이게 되어서 궁극적으로 Login이 가능한 사용자 기준으로 부여된 권한을 조회하기 위해서는 아래의 쿼리를 사용해야 합니다.

 

수행방법

SELECT 
    DB_NAME() as DB_Name,
        Login_Name = ul.[name],
        User_Type = 
    CASE mmbrp.[type] 
    WHEN 'G' THEN 'Windows Group' 
    WHEN 'S' THEN 'SQL User' 
    WHEN 'U' THEN 'Windows User' 
    END,
    Database_User_Name = mmbrp.[name],
    DB_Role = rolp.[name]
FROM 
    sys.database_role_members mmbr, -- The Role OR members associations table
    sys.database_principals rolp,     -- The DB Roles names table
    sys.database_principals mmbrp,    -- The Role members table (database users)
    sys.server_principals ul          -- The Login accounts table
WHERE 
     rolp.[principal_id] = mmbr.[role_principal_id]
    AND mmbrp.[principal_id] = mmbr.[member_principal_id]
    AND ul.[sid] = mmbrp.[sid]
ORDER BY ul.[name]

결과예시