가라멜의 달달한 세상 탐방

출처 : https://sqlgate.wordpress.com/2011/05/19/sql-server-%EC%82%AC%EC%9A%A9%EC%9E%90-%EC%A0%95%EB%B3%B4-%EB%B3%B4%EA%B8%B0-%EC%BF%BC%EB%A6%AC/

[SQL-Server] 사용자 정보 보기 쿼리

SQL-Server <결과>

sqlgate.wordpress.com

 










SELECT obj.principal_id as [ID],

       obj.name AS [Name],

       ISNULL(SUSER_SNAME(obj.sid), N'') as [Login],

       ISNULL(obj.default_schema_name, N'') as [Default Schema],

       CAST

       (

           CASE

               WHEN dp.state IN (N'G', N'W')

               THEN 1

               ELSE 0

           END AS bit

       ) as [Has DB Access] ,

       create_date as [Create Date] ,

       modify_date as [Modify Date]

  FROM sys.database_principals obj

   LEFT OUTER JOIN sys.database_permissions dp

       ON (

           dp.grantee_principal_id = obj.principal_id

       )

       AND

       (

           dp.type = N'CO'

       )

 WHERE (

           obj.type in (N'U', N'S', N'G', N'C', N'K')

       )