SQL Server에서 사용자 역할을 확인하는 방법

Netwrix Auditor for SQL Server

1. Netwrix Auditor를 열고 Reports -> Predefined -> SQL Server - State-in-Time -> SQL Server의 Account Permissions로 이동합니다.
2. 다음 필터를 지정합니다.
   • 사용자 계정 필터에서 전체 사용자 이름을 입력합니다(예: MILKYWAY/TomSimpson).
   • 개체 유형 필터에서 다음을 선택합니다: Server Instance, Database.
3. 보기를 클릭하여 사용자에 대한 유효 권한을 명확하게 정리한 보고서를 생성합니다.

네이티브 솔루션

사용자에 대한 SQL Server 역할 나열

1. 시작 Microsoft SQL Server Management Studio (MSSMS).
2. 상단의 File 메뉴에서 Connect Object Explorer를 클릭합니다.
3. Connect to Server 대화 상자에서 다음 설정을 지정합니다:
   • ‘서버 유형’Database Engine’를 선택하세요.
   • ‘서버 이름’ 텍스트 상자에 SQL 클러스터 서버의 이름을 입력하세요.
   • ‘인증’ 드롭다운 목록에서 ‘SQL Server Authenticationmethod’를 선택하고 사용할 자격 증명을 지정하세요. 서버에 연결할 때마다 암호를 다시 입력하고 싶지 않다면 ‘비밀번호 저장’ 옵션을 선택하세요.
4. ‘연결’을 클릭하세요.
5. ‘실행’을 클릭하거나(또는 ‘F5’ 키를 누르세요).
6. 연결되면 New Query 다음 스크립트를 쿼리 필드에 붙여넣으세요:

      select r.name as Role, m.name as Principal

from

    master.sys.server_role_members rm

    inner join

    master.sys.server_principals r on r.principal_id = rm.role_principal_id and r.type = 'R'

    inner join

    master.sys.server_principals m on m.principal_id = rm.member_principal_id

where m.name = 'MILKYWAY\TomSimpson'
      

• 쿼리 실행 결과에서 서버 수준 역할과 주체(멤버 이름) 목록을 확인하세요:

사용자에 대해 SQL Server에서 데이터베이스 역할 조회

1. 시작 Microsoft SQL Server Management Studio (MSSMS) 입니다.
2. 파일 메뉴에서 Connect Object Explorer 를 클릭하세요.
3. Connect to Server 대화 상자에서 다음 설정을 지정하십시오:
   • Server type 목록 상자에서Database Engine을(를) 선택합니다.
   • Server name 텍스트 상자에 SQL 클러스터 서버의 이름을 입력합니다.
   • Authentication 목록 상자에서 SQL Server Authentication method 를 선택하고 사용할 자격 증명을 지정합니다. 서버에 연결할 때마다 암호를 다시 입력하고 싶지 않다면 Remember password 를 선택(체크)하십시오.
4. Connect 를 클릭합니다.
5. 클릭 Execute (또는 F5 키를 누르세요).
6. 연결하면 Database 사용자 역할을 조회할 데이터베이스를 선택하세요.
7. 클릭 New Query 를 선택한 다음 아래 스크립트를 쿼리 입력란에 붙여넣으세요:

      SELECT r.name role_principal_name, m.name AS member_principal_name

FROM sys.database_role_members rm 

JOIN sys.database_principals r 

    ON rm.role_principal_id = r.principal_id

JOIN sys.database_principals m 

    ON rm.member_principal_id = m.principal_id

where m.name = 'MILKYWAY\TomSimpson'
      

• 쿼리 실행 결과에서 서버 수준 역할 및 프린서펄(멤버 이름) 목록을 검토하세요:
  

단 한 번의 쿼리도 없이 SQL Server에서 사용자 역할을 확인하는 방법을 알아보세요.

Microsoft SQL Server는 데이터베이스 관리자가 구조화된 데이터에 대한 권한을 관리할 수 있도록 역할을 제공합니다. 서버 수준 역할은 이름 그대로 서버 전체에 대한 액세스를 부여하며, Windows 세계의 그룹과 유사합니다. 또한 각 SQL 데이터베이스에는 고유한 권한과 역할이 있을 수도 있습니다.

보안을 유지하고 PCI DSS 및 HIPAA를 포함한 여러 규정을 준수하려면 각 사용자에게 할당된 모든 서버 및 데이터베이스 역할을 알아야 합니다. 관련성이 복잡하기 때문에, 사용 가능한 것이 기본 제공 도구뿐이라면 이 작업은 쉽지 않습니다.

먼저 서버 역할, 권한, 사용자 자격 증명, 종속성과 같은 서버 수준 설정은 master 데이터베이스에 저장됩니다. sys.server_principals 시스템 뷰를 사용하면 됩니다.,

• SQL Server에서 사용자와 서버 역할을 나열하려면 다음과 같은 시스템 뷰를 쿼리할 수 있습니다: sys.server_principals.
• SQL Server에서 데이터베이스의 사용자와 역할을 나열하려면 sys.database_principals 같은 시스템 뷰를 쿼리할 수 있습니다.

저장 프로시저는 서버의 일부 영역을 관리하는 데 도움이 될 수 있지만, 사용자 지정 보고서를 만들기 위해서는 쿼리를 사용해야 합니다(예: 특정 열 이름으로 여러 테이블을 매칭하는 보고서). 예를 들어, 서버 수준 역할 멤버십 정보는 master 데이터베이스의 server_role_members 시스템 뷰에 저장됩니다. 주체(principal)의 ID가 연결되어 있으므로, ID 번호를 기준으로 sys.server_principals를 master.sys.server_role_members와 조인하는 쿼리를 통해 SQL Server 사용자 역할 요약을 얻을 수 있습니다. 사용자는 고정 서버 역할(fixed server roles) 각각의 구성원에 대한 서버 역할 멤버십과 principal ID를 볼 수 있지만, 모든 서버 역할 멤버십을 보려면 추가 권한이 필요하거나 security admin 고정 서버 역할에 속해야 한다는 점을 기억하세요.

데이터베이스 수준 정보를 수집하려면 각 데이터베이스에서 SQL Server 데이터베이스 역할을 개별적으로 쿼리해야 하므로 시간이 많이 걸릴 수 있습니다. 또한 역할은 중첩될 수 있습니다. 즉, 데이터베이스 사용자, 애플리케이션 역할, 그리고 다른 데이터베이스 역할이 데이터베이스 역할의 구성원이 될 수 있습니다.

요약하자면, 기본 도구로 현재 사용자 역할에 대한 포괄적인 정보를 얻는 일은 복잡할 수 있고 매우 지치게 만들 수 있습니다. 반면 Netwrix Auditor를 사용하면 몇 번의 클릭만으로 누가 어떤 서버 및 데이터베이스 역할을 보유하는지에 대한 자세한 정보를 읽기 쉬운 형식으로 확인할 수 있습니다. 제품에 포함된 연결된 보고서는 전문가들이 전체 서버에 걸친 중첩 사용자 역할 멤버십의 복잡한 내용을 빠르게 파악하고 조사를 간소화하는 데 도움이 됩니다. 필요한 핵심 세부 정보가 모두 제공됩니다. 즉, 사용자가 접근할 수 있는 모든 개체 목록(경로 및 개체 유형 포함), 부여된 권한, 권한이 부여된 방식(직접, 역할 멤버십을 통해 등), 그리고 권한이 명시적인지 아니면 상속된 것인지 여부를 확인할 수 있습니다. 계정, 개체, 서버 수준을 포함해 다양한 관점에서 권한을 분석할 수 있습니다. 이러한 모든 정보는 스크립트를 작성하거나 정보를 Excel로 내보내거나 수동으로 일일이 검토하는 번거로움 없이 제시되고 즉시 사용할 수 있습니다.