[MSSQL] Login 계정 및 권한 조회

MSSQL User, Role 구조

 

1)

SELECT
    princ.name AS [User_Name],
    princ.type_desc AS [User_Type],
    perm.permission_name,
    perm.state_desc AS [Permission_State],
    OBJECT_NAME(perm.major_id) AS [Table_Name]
FROM   
    sys.database_principals princ
    LEFT JOIN sys.database_permissions perm
        ON princ.principal_id = perm.grantee_principal_id
WHERE  
    princ.type_desc NOT IN ('DATABASE_ROLE', 'APPLICATION_ROLE')
ORDER BY 
    princ.name, perm.permission_name;

2) 

select sl.name, sl.sysadmin, sl.createdate, tt.*
from sys.syslogins as sl
left join (
	SELECT DatabaseUserName, 
			case when [db_datareader] is null then '' else 'O' end as [db_datareader],
			case when [db_datawriter] is null then '' else 'O' end as [db_datawriter],
			case when [db_ddladmin] is null then '' else 'O' end as [db_ddladmin],
			case when [db_owner] is null then '' else 'O' end as [db_owner],
			case when [eland_operator] is null then '' else 'O' end as [eland_operator],
			case when [sysadmin] is null then '' else 'O' end as [sysadmin]
	FROM (
			SELECT isnull (DP2.name, 'No members') as DatabaseUserName, DP1.name as DatabaseRoleName FROM sys.database_role_members AS DRM
			RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id
			LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id
			WHERE DP1.type = 'R' UNION SELECT name, 'sysadmin' as DatabaseRoleName FROM sys.syslogins where sysadmin=1 and name not like 'NT SERVICE%' ) AS result
			PIVOT (MAX(DatabaseRoleName) FOR DatabaseRoleName IN ([db_datareader],[db_datawriter], [db_ddladmin],[db_owner],[eland_operator],[sysadmin])) AS pivot_result
			WHERE DatabaseUserName NOT IN ('dbo', 'No members')
	) as tt on sl.name=tt.DatabaseUserName
order by createdate desc;

 

3)

SELECT
obj.name AS [ObjectName],
schema_name(obj.schema_id) AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
obj.type AS [ObjectType],
0 AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.all_objects AS obj ON obj.object_id = prmssn.major_id and prmssn.class = 1
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name='public')
UNION
SELECT
obj.name AS [ObjectName],
null AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
null AS [ObjectType],
0 AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.schemas AS obj ON obj.schema_id = prmssn.major_id and prmssn.class = 3
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name='public')
UNION
SELECT
obj.name AS [ObjectName],
null AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
null AS [ObjectType],
0 AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.database_principals AS obj ON obj.principal_id = prmssn.major_id and prmssn.class = 4
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name='public')
UNION
SELECT
obj.name AS [ObjectName],
null AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
null AS [ObjectType],
0 AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.assemblies AS obj ON obj.assembly_id = prmssn.major_id and prmssn.class = 5
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name='public')
UNION
SELECT
obj.name AS [ObjectName],
SCHEMA_NAME(obj.schema_id) AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
null AS [ObjectType],
obj.is_table_type AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.types AS obj ON obj.user_type_id = prmssn.major_id and prmssn.class = 6
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name='public')
UNION
SELECT
obj.name AS [ObjectName],
null AS [ObjectSchema],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [ObjectClass],
null AS [ObjectType],
0 AS [IsTableType]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.fulltext_catalogs AS obj ON obj.fulltext_catalog_id = prmssn.major_id and prmssn.class = 23
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id