user가 가지고 있는 권한 확인

--user별 권한 확인(특정DB에서만 전체DB검색하는건 아래쪽에 있음)

SELECT

    [DatabaseName] = DB_NAME(),     

       [LoginName] = ulogin.[name],

    [DBUserName] = princ.[name],

    [UserType] = CASE princ.[type]

                    WHEN 'S' THEN 'SQL User'

                    WHEN 'U' THEN 'Windows User'

                 END,

    [Role] = null,    

    [PermissionType] = perm. [permission_name],     

    [PermissionState] = perm. [state_desc],     

    [ObjectType] = obj. type_desc,--perm.[class_desc],     

    [ObjectName] = OBJECT_NAME( perm.major_id ),

    [ColumnName] = col. [name]

FROM  

    --database user

    sys.database_principals princ

LEFT JOIN

    --Login accounts

--     select top 100 * from sys.login_token

    sys.syslogins ulogin on princ .[sid] = ulogin .[sid]

LEFT JOIN       

    --Permissions

    sys.database_permissions perm ON perm .[grantee_principal_id] = princ .[principal_id]

LEFT JOIN

    --Table columns

    sys.columns col ON col .[object_id] = perm .major_id

                    AND col .[column_id] = perm .[minor_id]

LEFT JOIN

    sys.objects obj ON perm .[major_id] = obj .[object_id]

WHERE

    princ.[type] in ('S', 'U')

UNION

--List all access provisioned to a sql user or windows user/group through a database or application role

SELECT

       [DatabaseName] = DB_NAME(), 

    [LoginName] = ulogin.[name],

    [DBUserName] = roleprinc.[name],

    [UserType] = CASE memberprinc.[type]

                    WHEN 'S' THEN 'SQL User'

                    WHEN 'U' THEN 'Windows User'

                 END,

    [Role] = roleprinc. [name],    

    [PermissionType] = perm. [permission_name],     

    [PermissionState] = perm. [state_desc],     

    [ObjectType] = obj. type_desc,--perm.[class_desc], 

    [ObjectName] = OBJECT_NAME( perm.major_id ),

    [ColumnName] = col. [name]

FROM  

    --Role/member associations

    sys.database_role_members members

JOIN

    --Roles

    sys.database_principals roleprinc ON roleprinc .[principal_id] = members .[role_principal_id]

JOIN

    --Role members (database users)

    sys.database_principals memberprinc ON memberprinc .[principal_id] = members .[member_principal_id]

LEFT JOIN

    --Login accounts

    sys.syslogins ulogin on memberprinc .[sid] = ulogin .[sid]

LEFT JOIN       

    --Permissions

    sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]

LEFT JOIN

    --Table columns

    sys.columns col on col .[object_id] = perm .major_id

                    AND col .[column_id] = perm .[minor_id]

LEFT JOIN

    sys.objects obj ON perm .[major_id] = obj .[object_id]

UNION

--List all access provisioned to the public role, which everyone gets by default

SELECT

       [DatabaseName] = DB_NAME(),

    [LoginName] = '{All Users}',

       [DBUserName] = '{All Users}',

    [UserType] = '{All Users}',

    [Role] = roleprinc. [name],    

    [PermissionType] = perm. [permission_name],     

    [PermissionState] = perm. [state_desc],     

    [ObjectType] = obj. type_desc,--perm.[class_desc],

    [ObjectName] = OBJECT_NAME( perm.major_id ),

    [ColumnName] = col. [name]

FROM  

    --Roles

    sys.database_principals roleprinc

LEFT JOIN       

    --Role permissions

    sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]

LEFT JOIN

    --Table columns

    sys.columns col on col .[object_id] = perm .major_id

                    AND col .[column_id] = perm .[minor_id]                 

JOIN

    --All objects 

    sys.objects obj ON obj .[object_id] = perm .[major_id]

WHERE

    --Only roles

    roleprinc.[type] = 'R' AND

    --Only public role

    roleprinc.[name] = 'public' AND

    --Only objects of ours, not the MS objects

    obj.is_ms_shipped = 0

ORDER BY

    princ.[Name] ,

    OBJECT_NAME(perm .major_id),

    col.[name] ,

    perm.[permission_name] ,

    perm.[state_desc] ,

    obj.type_desc --perm.[class_desc]

--user별 권한 확인(전체DB를 돌면서 진행)

set nocount on

declare @permission table (

Database_Name sysname,

LoginName sysname null,

DBUserName sysname null,

UserType sysname null,

[role] sysname null,

PermissionType sysname null,

PermissionState sysname null,

ObjectType sysname null,

ObjectName sysname null,

ColumnName sysname null

)

declare @dbs table (dbname sysname)

declare @Next sysname

insert into @dbs

select name from sys.databases order by name

select top 1 @Next = dbname from @dbs

while (@@rowcount<>0)

begin

insert into @permission

exec('use [' + @Next + ']

SELECT

    [DatabaseName] = DB_NAME(),     

       [LoginName] = ulogin.[name],

    [DBUserName] = princ.[name],

    [UserType] = CASE princ.[type]

                    WHEN ''S'' THEN ''SQL User''

                    WHEN ''U'' THEN ''Windows User''

                 END,

    [Role] = null,    

    [PermissionType] = perm. [permission_name],     

    [PermissionState] = perm. [state_desc],     

    [ObjectType] = obj. type_desc,--perm.[class_desc],     

    [ObjectName] = OBJECT_NAME( perm.major_id ),

    [ColumnName] = col. [name]

FROM  

    sys.database_principals princ

LEFT JOIN

    sys.syslogins ulogin on princ .[sid] = ulogin .[sid]

LEFT JOIN       

    --Permissions

    sys.database_permissions perm ON perm .[grantee_principal_id] = princ .[principal_id]

LEFT JOIN

    --Table columns

    sys.columns col ON col .[object_id] = perm .major_id

                    AND col .[column_id] = perm .[minor_id]

LEFT JOIN

    sys.objects obj ON perm .[major_id] = obj .[object_id]

WHERE

    princ.[type] in (''S'', ''U'')

UNION

--List all access provisioned to a sql user or windows user/group through a database or application role

SELECT

       [DatabaseName] = DB_NAME(), 

    [LoginName] = ulogin.[name],

    [DBUserName] = roleprinc.[name],

    [UserType] = CASE memberprinc.[type]

                    WHEN ''S'' THEN ''SQL User''

                    WHEN ''U'' THEN ''Windows User''

                 END,

    [Role] = roleprinc. [name],    

    [PermissionType] = perm. [permission_name],     

    [PermissionState] = perm. [state_desc],     

    [ObjectType] = obj. type_desc,--perm.[class_desc], 

    [ObjectName] = OBJECT_NAME( perm.major_id ),

    [ColumnName] = col. [name]

FROM  

    --Role/member associations

    sys.database_role_members members

JOIN

    --Roles

    sys.database_principals roleprinc ON roleprinc .[principal_id] = members .[role_principal_id]

JOIN

    --Role members (database users)

    sys.database_principals memberprinc ON memberprinc .[principal_id] = members .[member_principal_id]

LEFT JOIN

    --Login accounts

    sys.syslogins ulogin on memberprinc .[sid] = ulogin .[sid]

LEFT JOIN       

    --Permissions

    sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]

LEFT JOIN

    --Table columns

    sys.columns col on col .[object_id] = perm .major_id

                    AND col .[column_id] = perm .[minor_id]

LEFT JOIN

    sys.objects obj ON perm .[major_id] = obj .[object_id]

UNION

--List all access provisioned to the public role, which everyone gets by default

SELECT

       [DatabaseName] = DB_NAME(),

    [LoginName] = ''{All Users}'',

       [DBUserName] = ''{All Users}'',

    [UserType] = ''{All Users}'',

    [Role] = roleprinc. [name],    

    [PermissionType] = perm. [permission_name],     

    [PermissionState] = perm. [state_desc],     

    [ObjectType] = obj. type_desc,--perm.[class_desc],

    [ObjectName] = OBJECT_NAME( perm.major_id ),

    [ColumnName] = col. [name]

FROM  

    --Roles

    sys.database_principals roleprinc

LEFT JOIN       

    --Role permissions

    sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]

LEFT JOIN

    --Table columns

    sys.columns col on col .[object_id] = perm .major_id

                    AND col .[column_id] = perm .[minor_id]                 

JOIN

    --All objects 

    sys.objects obj ON obj .[object_id] = perm .[major_id]

WHERE

    --Only roles

    roleprinc.[type] = ''R'' AND

    --Only public role

    roleprinc.[name] = ''public'' AND

    --Only objects of ours, not the MS objects

    obj.is_ms_shipped = 0

ORDER BY

    princ.[Name] ,

    OBJECT_NAME(perm .major_id),

    col.[name] ,

    perm.[permission_name] ,

    perm.[state_desc] ,

    obj.type_desc --perm.[class_desc]'

)

delete @dbs where dbname = @Next

select top 1 @Next = dbname from @dbs

end

set nocount off

select * from @permission