--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를 돌면서 진행)
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
