데이터베이스의 모든 사용자에 대한 모든 권한/액세스를 찾기 위한 SQL Server 쿼리
SQL 2008에 쿼리를 작성하여 직접 또는 역할 등에 의해 특정 데이터베이스 또는 데이터베이스 내의 객체(테이블, 뷰, 스토어드 프로시저 등)에 액세스할 수 있는 모든 사용자를 보고합니다.이 보고서는 보안 감사 목적으로 사용됩니다.제 요구에 딱 맞는 질문을 가지고 계신 분이 있을지 모르겠지만, 좋은 출발을 할 수 있기를 바랍니다.sql 2008, 2005, 2000 중 하나로 충분합니다.필요에 따라 변환할 수 있습니다.
Andomar의 제안을 바탕으로 한 질의는 이번이 처음입니다.이 쿼리는 사용자가 사용자 계정에 직접 적용했거나 사용자가 가진 역할을 통해 적용한 권한 목록을 제공하기 위한 것입니다.
/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role
Columns Returned:
UserName : SQL or Windows/Active Directory user account. This could also be an Active Directory group.
UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the
SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the
same as the server user.
Role : The role name. This will be null if the associated permissions to the object are defined at directly
on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE,
SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectName : Name of the object that the user/role is assigned permissions on.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value
is only populated if the object is a table, view or a table value function.
*/
--List all access provisioned to a sql user or windows user/group directly
SELECT
[UserName] = CASE princ.[type]
WHEN 'S' THEN princ.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = princ.[name],
[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
sys.login_token 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
[UserName] = CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = memberprinc.[name],
[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.login_token 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
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[DatabaseUserName] = '{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]
다음은 Brad(2011년 10월)와 iw가 제안한 변경사항에 대한 Jeremy의 2011년 8월 질문의 전체 버전입니다.Kuchin(2012년 5월) 법인:
- Brad: 정답
[ObjectType]★★★★★★★★★★★★★★★★★」[ObjectName]스키마용입니다. - iw.kuchin:위해서
[ObjectType]것이 좋다obj.type_desc★★★★★★★★★★★★★★★★★만OBJECT_OR_COLUMN모든 에는 을 사용합니다.perm.[class_desc]. - iw.kuchin:핸들
IMPERSONATE권한을 부여합니다. - iw.kuchin: 교환
sys.login_tokensys.server_principalsWindows SQL windows windows windows windows 。 - iw.kuchin:Windows 그룹을 포함합니다.
- iw.kuchin: 사용자 제외
sysINFORMATION_SCHEMA INFORMATION_SCHEMA.
이걸로 다른 사람이 한두 시간 더 살 수 있길 바라. :)
/*
Security Audit Report
1) List all access provisioned to a SQL user or Windows user/group directly
2) List all access provisioned to a SQL user or Windows user/group through a database or application role
3) List all access provisioned to the public role
Columns Returned:
UserType : Value will be either 'SQL User', 'Windows User', or 'Windows Group'.
This reflects the type of user/group defined for the SQL Server account.
DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the
same as the server user.
LoginName : SQL or Windows/Active Directory user account. This could also be an Active Directory group.
Role : The role name. This will be null if the associated permissions to the object are defined at directly
on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE,
SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
Schema : Name of the schema the object is in.
ObjectName : Name of the object that the user/role is assigned permissions on.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value
is only populated if the object is a table, view or a table value function.
*/
--1) List all access provisioned to a SQL user or Windows user/group directly
SELECT
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[LoginName] = ulogin.[name],
[Role] = NULL,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Database user
sys.database_principals AS princ
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid]
--Permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G')
-- No need for these system accounts
AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
UNION
--2) List all access provisioned to a SQL user or Windows user/group through a database or application role
SELECT
[UserType] = CASE membprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = membprinc.[name],
[LoginName] = ulogin.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Role/member associations
sys.database_role_members AS members
--Roles
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
--Role members (database users)
JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid]
--Permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
membprinc.[type] IN ('S','U','G')
-- No need for these system accounts
AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
UNION
--3) List all access provisioned to the public role, which everyone gets by default
SELECT
[UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[LoginName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Roles
sys.database_principals AS roleprinc
--Role permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
--All objects
JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
roleprinc.[type] = 'R'
AND roleprinc.[name] = 'public'
AND obj.[is_ms_shipped] = 0
ORDER BY
[UserType],
[DatabaseUserName],
[LoginName],
[Role],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType]
SQL Server 2005부터는 시스템뷰를 사용할 수 있습니다.예를 들어, 이 쿼리는 데이터베이스 내의 모든 사용자를 권한과 함께 나열합니다.
select princ.name
, princ.type_desc
, perm.permission_name
, perm.state_desc
, perm.class_desc
, object_name(perm.major_id)
from sys.database_principals princ
left join
sys.database_permissions perm
on perm.grantee_principal_id = princ.principal_id
사용자는 역할을 통해서도 권한을 가질 수 있습니다.를 들면, 「」는,db_data_reader grants " " 。select대부분의 개체에 대한 권한입니다.
받아들여진 답변에 코멘트를 할 수 없기 때문에 여기에 코멘트를 추가합니다.
- 나는 스키마 문제에 있어서 브래드를 지지한다. 레퍼런스 MS " " " 에서
sys.objects테이블에는 스키마 범위 객체만 포함되어 있습니다. " 수준" 오브젝트대한 "상위 수준" 오브젝트를 사용해야 .sys.schemastable.syslog를 클릭합니다. - ★★★의
[ObjectType]것이 좋다obj.type_desc★★★★★★★★★★★★★★★★★만OBJECT_OR_COLUMN모든 에는 을 사용합니다.perm.[class_desc] - 잘되지 않는 또 는 '허락'입니다.
IMPERSONATE에 대한 , 「」를 해 주세요LEFT JOINsys.database_principalsperm.major_id = imp.principal_id - 더 낫습니다.
sys.login_tokensys.server_principals뿐만 아니라 로그인뿐만 아니라 SQL 로그인도 됩니다. - one one
'G'Windows 。 - 유저는 할 수 .
sys★★★★★★★★★★★★★★★★★」INFORMATION_SCHEMA는 서비스 '''에 테이블에서할 수 있습니다.
제안된 모든 수정 사항과 함께 첫 번째 스크립트를 게시합니다. 다른 부분도 변경해야 합니다.
SELECT
[UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.server_principals 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]
LEFT JOIN
sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G') AND
-- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
제가 본 다른 답변은 데이터베이스에서 가능한 몇 가지 권한을 놓쳤습니다.아래 코드의 첫 번째 쿼리는 시스템 개체가 아닌 모든 항목에 대한 데이터베이스 수준 권한을 가져옵니다.또한 적절한 GRANT 스테이트먼트도 생성합니다.두 번째 쿼리는 모든 역할 권한을 가집니다.
이 작업은 각 데이터베이스에 대해 수행해야 하지만 sp_MSoreachdb에서 사용하기에는 너무 깁니다.이렇게 하려면 마스터 데이터베이스에 시스템 저장 프로시저로 추가해야 합니다.
모든 가능성을 해결하려면 서버 수준의 권한을 확인하는 스크립트도 필요합니다.
SELECT DB_NAME() AS database_name
, class
, class_desc
, major_id
, minor_id
, grantee_principal_id
, grantor_principal_id
, databasepermissions.type
, permission_name
, STATE
, state_desc
, granteedatabaseprincipal.name AS grantee_name
, granteedatabaseprincipal.type_desc AS grantee_type_desc
, granteeserverprincipal.name AS grantee_principal_name
, granteeserverprincipal.type_desc AS grantee_principal_type_desc
, grantor.name AS grantor_name
, granted_on_name
, permissionstatement + N' TO ' + QUOTENAME(granteedatabaseprincipal.name) + CASE
WHEN STATE = N'W'
THEN N' WITH GRANT OPTION'
ELSE N''
END AS permissionstatement
FROM (
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(CONVERT(NVARCHAR(MAX), DB_NAME())) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS permissionstatement
FROM sys.database_permissions
WHERE (sys.database_permissions.class = 0)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) + COALESCE(N' (' + QUOTENAME(sys.columns.name) + N')', N'') AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.objects
ON sys.objects.object_id = sys.database_permissions.major_id
INNER JOIN sys.schemas
ON sys.schemas.schema_id = sys.objects.schema_id
LEFT OUTER JOIN sys.columns
ON sys.columns.object_id = sys.database_permissions.major_id
AND sys.columns.column_id = sys.database_permissions.minor_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 1)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.schemas.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SCHEMA::' + QUOTENAME(sys.schemas.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.schemas
ON sys.schemas.schema_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 3)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(targetPrincipal.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + targetPrincipal.type_desc + N'::' + QUOTENAME(targetPrincipal.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.database_principals AS targetPrincipal
ON targetPrincipal.principal_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 4)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.assemblies.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASSEMBLY::' + QUOTENAME(sys.assemblies.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.assemblies
ON sys.assemblies.assembly_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 5)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.types.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.types
ON sys.types.user_type_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 6)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.types.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.types
ON sys.types.user_type_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 6)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.xml_schema_collections.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON XML SCHEMA COLLECTION::' + QUOTENAME(sys.xml_schema_collections.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.xml_schema_collections
ON sys.xml_schema_collections.xml_collection_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 10)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON MESSAGE TYPE::' + QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.service_message_types
ON sys.service_message_types.message_type_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 15)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CONTRACT::' + QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.service_contracts
ON sys.service_contracts.service_contract_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 16)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SERVICE::' + QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.services
ON sys.services.service_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 17)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON REMOTE SERVICE BINDING::' + QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.remote_service_bindings
ON sys.remote_service_bindings.remote_service_binding_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 18)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ROUTE::' + QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.routes
ON sys.routes.route_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 19)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.symmetric_keys.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.symmetric_keys.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.symmetric_keys
ON sys.symmetric_keys.symmetric_key_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 24)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.certificates.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CERTIFICATE::' + QUOTENAME(sys.certificates.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.certificates
ON sys.certificates.certificate_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 25)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.asymmetric_keys.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.asymmetric_keys.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.asymmetric_keys
ON sys.asymmetric_keys.asymmetric_key_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 26)
) AS databasepermissions
INNER JOIN sys.database_principals AS granteedatabaseprincipal
ON granteedatabaseprincipal.principal_id = grantee_principal_id
LEFT OUTER JOIN sys.server_principals AS granteeserverprincipal
ON granteeserverprincipal.sid = granteedatabaseprincipal.sid
INNER JOIN sys.database_principals AS grantor
ON grantor.principal_id = grantor_principal_id
ORDER BY grantee_name, granted_on_name
SELECT roles.name AS role_name
, roles.principal_id
, roles.type AS role_type
, roles.type_desc AS role_type_desc
, roles.is_fixed_role AS role_is_fixed_role
, memberdatabaseprincipal.name AS member_name
, memberdatabaseprincipal.principal_id AS member_principal_id
, memberdatabaseprincipal.type AS member_type
, memberdatabaseprincipal.type_desc AS member_type_desc
, memberdatabaseprincipal.is_fixed_role AS member_is_fixed_role
, memberserverprincipal.name AS member_principal_name
, memberserverprincipal.type_desc member_principal_type_desc
, N'ALTER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberdatabaseprincipal.name) AS AddRoleMembersStatement
FROM sys.database_principals AS roles
INNER JOIN sys.database_role_members
ON sys.database_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals AS memberdatabaseprincipal
ON memberdatabaseprincipal.principal_id = sys.database_role_members.member_principal_id
LEFT OUTER JOIN sys.server_principals AS memberserverprincipal
ON memberserverprincipal.sid = memberdatabaseprincipal.sid
ORDER BY role_name
, member_name
업데이트: 다음 쿼리는 서버 수준의 권한 및 구성원 자격을 가져옵니다.
SELECT sys.server_permissions.class
, sys.server_permissions.class_desc
, sys.server_permissions.major_id
, sys.server_permissions.minor_id
, sys.server_permissions.grantee_principal_id
, sys.server_permissions.grantor_principal_id
, sys.server_permissions.type
, sys.server_permissions.permission_name
, sys.server_permissions.state
, sys.server_permissions.state_desc
, granteeserverprincipal.name AS grantee_principal_name
, granteeserverprincipal.type_desc AS grantee_principal_type_desc
, grantorserverprinicipal.name AS grantor_name
, CASE
WHEN sys.server_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.server_permissions.state_desc
END + N' ' + sys.server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' TO ' + QUOTENAME(granteeserverprincipal.name) AS permissionstatement
FROM sys.server_principals AS granteeserverprincipal
INNER JOIN sys.server_permissions
ON sys.server_permissions.grantee_principal_id = granteeserverprincipal.principal_id
INNER JOIN sys.server_principals AS grantorserverprinicipal
ON grantorserverprinicipal.principal_id = sys.server_permissions.grantor_principal_id
ORDER BY granteeserverprincipal.name
, sys.server_permissions.permission_name
SELECT roles.name AS server_role_name
, roles.principal_id
, roles.type AS role_type
, roles.type_desc AS role_type_desc
, roles.is_fixed_role AS role_is_fixed_role
, memberserverprincipal.name AS member_principal_name
, memberserverprincipal.principal_id AS member_principal_id
, memberserverprincipal.type AS member_principal_type
, memberserverprincipal.type_desc AS member_principal_type_desc
, memberserverprincipal.is_fixed_role AS member_is_fixed_role
, N'ALTER SERVER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberserverprincipal.name) AS AddRoleMembersStatement
FROM sys.server_principals AS roles
INNER JOIN sys.server_role_members
ON sys.server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS memberserverprincipal
ON memberserverprincipal.principal_id = sys.server_role_members.member_principal_id
WHERE roles.type = N'R'
ORDER BY server_role_name
, member_principal_name
멋진 대본 제레미와 기고자들!감사합니다!
많은 사용자가 있기 때문에 모든 사용자에게 이 작업을 실행하는 것은 악몽이었습니다.댓글을 달지 못해서 대본 전체를 변경해서 올립니다.변수 + where 구를 추가하여 사용자 이름(또는 공백인 경우 모든 사용자)에서 최대 5자까지 일치하는 항목을 검색할 수 있습니다.특별한 것은 없지만, 경우에 따라서는 도움이 될 것이라고 생각했습니다.
DECLARE @p_userName NVARCHAR(5) = 'UName' -- Specify up to five characters here (or none for all users)
/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role
Columns Returned:
UserName : SQL or Windows/Active Directory user cccount. This could also be an Active Directory group.
UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the same as the server user.
Role : The role name. This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT, DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions.
ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles. Some built in roles have implicit permission definitions.
ObjectName : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles. Some built in roles have implicit permission definitions.
ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function.
*/
DECLARE @userName NVARCHAR(4) = @p_UserName + '%'
--List all access provisioned to a sql user or windows user/group directly
SELECT
[UserName] = CASE princ.[type]
WHEN 'S' THEN princ.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = princ.[name],
[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
sys.login_token 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')
AND princ.[name] LIKE @userName -- Added this line --CSLAGLE
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
[UserName] = CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = memberprinc.[name],
[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.login_token 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]
WHERE memberprinc.[name] LIKE @userName -- Added this line --CSLAGLE
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[DatabaseUserName] = '{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]
여기 다른 사람들을 각색한 제 버전이 있습니다.지금 30분 동안 어떻게 이 아이디어를 냈는지 기억하려고 했는데, @Jeremy의 답변이 가장 큰 영감을 준 것 같습니다.혹시 버그를 도입했을까 봐 제레미의 답변을 갱신하고 싶지 않았기 때문에 여기에 제버전을 올립니다.
Kenneth Fisher의 T-SQL Tuesday에서 얻은 영감과 완전한 스크립트를 조합할 것을 제안합니다. 특정 사용자가 가지고 있는 권한:이를 통해 컴플라이언스/감사 질문에 하향식이 아닌 상향식으로 답변할 수 있습니다.
EXECUTE AS LOGIN = '<loginname>'
SELECT token.name AS GroupNames
FROM sys.login_token token
JOIN sys.server_principals grp
ON token.sid = grp.sid
WHERE token.[type] = 'WINDOWS GROUP'
AND grp.[type] = 'G'
REVERT
을 이해하려면 , 을 해 주세요.Contoso\DB_AdventureWorks_Accounting를 가진 Windows AD Contoso\John.Doe. John을 통해 합니다.Doe' server_principal 'Adventure Works'Contoso\DB_AdventureWorks_LoginsWindows AD if " John (존이 하는 권한)이라는 받은 '그냥'?없어?」라고 하면, 이하의 스크립트만으로 그 질문에 대답할 수 없습니다.되는 각 스크립트에 ).nameActive Directory "SID" "SID" "SID" "SID" "SID" 。
다음은 이러한 역룩업 로직이 포함되지 않은 스크립트입니다.
/*
--Script source found at : http://stackoverflow.com/a/7059579/1387418
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role
Columns Returned:
UserName : SQL or Windows/Active Directory user account. This could also be an Active Directory group.
UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the
SQL Server user account.
PrinciaplUserName: if UserName is not blank, then UserName else DatabaseUserName
PrincipalType : Possible values are 'SQL User', 'Windows User', 'Database Role', 'Windows Group'
DatabaseUserName : Name of the associated user as defined in the database user account. The database user may not be the
same as the server user.
Role : The role name. This will be null if the associated permissions to the object are defined at directly
on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE,
SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectName : Name of the object that the user/role is assigned permissions on.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value
is only populated if the object is a table, view or a table value function.
*/
DECLARE @HideDatabaseDiagrams BIT = 1;
--List all access provisioned to a sql user or windows user/group directly
SELECT
[UserName] = CASE dbprinc.[type]
WHEN 'S' THEN dbprinc.[name] -- SQL User
WHEN 'U' THEN sprinc.[name] -- Windows User
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
[UserType] = CASE dbprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE dbprinc.[type]
END,
[PrincipalUserName] = COALESCE(
CASE dbprinc.[type]
WHEN 'S' THEN dbprinc.[name] -- SQL User
WHEN 'U' THEN sprinc.[name] -- Windows User
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
dbprinc.[name]
),
[PrincipalType] = CASE dbprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN 'Database Role'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = dbprinc.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.[type_desc],--perm.[class_desc],
[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals dbprinc
LEFT JOIN
--Login accounts
sys.server_principals sprinc on dbprinc.[sid] = sprinc.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = dbprinc.[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
dbprinc.[type] in ('S','U')
AND CASE
WHEN @HideDatabaseDiagrams = 1 AND
dbprinc.[name] = 'guest'
AND (
(
obj.type_desc = 'SQL_SCALAR_FUNCTION'
AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
)
OR (
obj.type_desc = 'SQL_STORED_PROCEDURE'
AND OBJECT_NAME(perm.major_id) IN
(
N'sp_alterdiagram',
N'sp_creatediagram',
N'sp_dropdiagram',
N'sp_helpdiagramdefinition',
N'sp_helpdiagrams',
N'sp_renamediagram'
)
)
)
THEN 0
ELSE 1
END = 1
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
[UserName] = CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN sprinc.[name]
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
END,
[PrincipalUserName] = COALESCE(
CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN sprinc.[name]
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
memberprinc.[name]
),
[PrincipalType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN 'Database Role'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
[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.server_principals sprinc on memberprinc.[sid] = sprinc.[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]
WHERE
CASE
WHEN @HideDatabaseDiagrams = 1 AND
memberprinc.[name] = 'guest'
AND (
(
obj.type_desc = 'SQL_SCALAR_FUNCTION'
AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
)
OR (
obj.type_desc = 'SQL_STORED_PROCEDURE'
AND OBJECT_NAME(perm.major_id) IN
(
N'sp_alterdiagram',
N'sp_creatediagram',
N'sp_dropdiagram',
N'sp_helpdiagramdefinition',
N'sp_helpdiagrams',
N'sp_renamediagram'
)
)
)
THEN 0
ELSE 1
END = 1
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[PrincipalUserName] = '{All Users}',
[PrincipalType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
[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
AND CASE
WHEN @HideDatabaseDiagrams = 1 AND
roleprinc.[name] = 'public'
AND (
(
obj.type_desc = 'SQL_SCALAR_FUNCTION'
AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
)
OR (
obj.type_desc = 'SQL_STORED_PROCEDURE'
AND OBJECT_NAME(perm.major_id) IN
(
N'sp_alterdiagram',
N'sp_creatediagram',
N'sp_dropdiagram',
N'sp_helpdiagramdefinition',
N'sp_helpdiagrams',
N'sp_renamediagram'
)
)
)
THEN 0
ELSE 1
END = 1
ORDER BY
dbprinc.[Name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.[permission_name],
perm.[state_desc],
obj.type_desc--perm.[class_desc]
위의 GetPermissions Stored Procedure는 좋지만 Sp_msforeachdb를 사용합니다.이는 SQL 인스턴스에 공백이나 대시 및 기타 베스트 프랙티스 이외의 문자가 포함된 데이터베이스 이름이 있으면 중단됨을 의미합니다.Sp_msforeachdb를 사용하지 않고 로그인이 sysadmin 로그인(IsSysAdminLogin)인 경우 및 로그인이 고아 사용자(IsEmptyRow)인 경우 2개의 컬럼을 포함하는 버전을 작성했습니다.
USE [master] ;
GO
IF EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns')
AND [type] in (N'P',N'PC')
)
BEGIN
DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
END
GO
CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns
AS
SET NOCOUNT ON
;
BEGIN TRY
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#permission]')
)
DROP TABLE #permission
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]')
)
DROP TABLE #userroles_kk
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]')
)
DROP TABLE #rolemember_kk
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[##db_name]')
)
DROP TABLE ##db_name
;
DECLARE
@db_name VARCHAR(255)
,@sql_text VARCHAR(MAX)
;
SET @sql_text =
'CREATE TABLE ##db_name
(
LoginUserName VARCHAR(MAX)
,'
;
DECLARE cursDBs CURSOR FOR
SELECT [name]
FROM sys.databases
ORDER BY [name]
;
OPEN cursDBs
;
FETCH NEXT FROM cursDBs INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_text =
@sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX)
,'
FETCH NEXT FROM cursDBs INTO @db_name
END
CLOSE cursDBs
;
SET @sql_text =
@sql_text + 'IsSysAdminLogin CHAR(1)
,IsEmptyRow CHAR(1)
)'
--PRINT @sql_text
EXEC (@sql_text)
;
DEALLOCATE cursDBs
;
DECLARE
@RoleName VARCHAR(255)
,@UserName VARCHAR(255)
;
CREATE TABLE #permission
(
LoginUserName VARCHAR(255)
,databasename VARCHAR(255)
,[role] VARCHAR(255)
)
;
DECLARE cursSysSrvPrinName CURSOR FOR
SELECT [name]
FROM sys.server_principals
WHERE
[type] IN ( 'S', 'U', 'G' )
AND principal_id > 4
AND [name] NOT LIKE '##%'
ORDER BY [name]
;
OPEN cursSysSrvPrinName
;
FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #userroles_kk
(
databasename VARCHAR(255)
,[role] VARCHAR(255)
)
;
CREATE TABLE #rolemember_kk
(
dbrole VARCHAR(255)
,membername VARCHAR(255)
,membersid VARBINARY(2048)
)
;
DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR
SELECT [name]
FROM sys.databases
ORDER BY [name]
;
OPEN cursDatabases
;
DECLARE
@DBN VARCHAR(255)
,@sqlText NVARCHAR(4000)
;
FETCH NEXT FROM cursDatabases INTO @DBN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlText =
N'USE ' + QUOTENAME(@DBN) + ';
TRUNCATE TABLE #RoleMember_kk
INSERT INTO #RoleMember_kk
EXEC sp_helprolemember
INSERT INTO #UserRoles_kk
(DatabaseName,[Role])
SELECT db_name(),dbRole
FROM #RoleMember_kk
WHERE MemberName = ''' + @UserName + '''
'
--PRINT @sqlText ;
EXEC sp_executesql @sqlText ;
FETCH NEXT FROM cursDatabases INTO @DBN
END
CLOSE cursDatabases
;
DEALLOCATE cursDatabases
;
INSERT INTO #permission
SELECT
@UserName 'user'
,b.name
,u.[role]
FROM
sys.sysdatabases b
LEFT JOIN
#userroles_kk u
ON QUOTENAME(u.databasename) = QUOTENAME(b.name)
ORDER BY 1
;
DROP TABLE #userroles_kk
;
DROP TABLE #rolemember_kk
;
FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
END
CLOSE cursSysSrvPrinName
;
DEALLOCATE cursSysSrvPrinName
;
TRUNCATE TABLE ##db_name
;
DECLARE
@d1 VARCHAR(MAX)
,@d2 VARCHAR(MAX)
,@d3 VARCHAR(MAX)
,@ss VARCHAR(MAX)
;
DECLARE cursPermisTable CURSOR FOR
SELECT * FROM #permission
ORDER BY 2 DESC
;
OPEN cursPermisTable
;
FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS
(
SELECT 1 FROM ##db_name WHERE LoginUserName = @d1
)
BEGIN
SET @ss =
'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')'
EXEC (@ss)
;
SET @ss =
'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''''
EXEC (@ss)
;
END
ELSE
BEGIN
DECLARE
@var NVARCHAR(MAX)
,@ParmDefinition NVARCHAR(MAX)
,@var1 NVARCHAR(MAX)
;
SET @var =
N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + ''''
;
SET @ParmDefinition =
N'@var1 NVARCHAR(600) OUTPUT '
;
EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT
;
SET @var1 =
ISNULL(@var1, ' ')
;
SET @var =
' UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''' '
;
EXEC (@var)
;
END
FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
END
CLOSE cursPermisTable
;
DEALLOCATE cursPermisTable
;
UPDATE ##db_name SET
IsSysAdminLogin = 'Y'
FROM
##db_name TT
INNER JOIN
dbo.syslogins SL
ON TT.LoginUserName = SL.[name]
WHERE
SL.sysadmin = 1
;
DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR
SELECT [name]
FROM tempdb.sys.columns
WHERE
OBJECT_ID = OBJECT_ID('tempdb..##db_name')
AND [name] NOT IN ('LoginUserName','IsEmptyRow')
ORDER BY [name]
;
OPEN cursDNamesAsColumns
;
DECLARE
@ColN VARCHAR(255)
,@tSQLText NVARCHAR(4000)
;
FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tSQLText =
N'UPDATE ##db_name SET
IsEmptyRow = ''N''
WHERE IsEmptyRow IS NULL
AND ' + QUOTENAME(@ColN) + ' IS NOT NULL
;
'
--PRINT @tSQLText ;
EXEC sp_executesql @tSQLText ;
FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
END
CLOSE cursDNamesAsColumns
;
DEALLOCATE cursDNamesAsColumns
;
UPDATE ##db_name SET
IsEmptyRow = 'Y'
WHERE IsEmptyRow IS NULL
;
UPDATE ##db_name SET
IsSysAdminLogin = 'N'
FROM
##db_name TT
INNER JOIN
dbo.syslogins SL
ON TT.LoginUserName = SL.[name]
WHERE
SL.sysadmin = 0
;
SELECT * FROM ##db_name
;
DROP TABLE ##db_name
;
DROP TABLE #permission
;
END TRY
BEGIN CATCH
DECLARE
@cursDBs_Status INT
,@cursSysSrvPrinName_Status INT
,@cursDatabases_Status INT
,@cursPermisTable_Status INT
,@cursDNamesAsColumns_Status INT
;
SELECT
@cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs')
,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName')
,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases')
,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
;
IF @cursDBs_Status > -2
BEGIN
CLOSE cursDBs ;
DEALLOCATE cursDBs ;
END
IF @cursSysSrvPrinName_Status > -2
BEGIN
CLOSE cursSysSrvPrinName ;
DEALLOCATE cursSysSrvPrinName ;
END
IF @cursDatabases_Status > -2
BEGIN
CLOSE cursDatabases ;
DEALLOCATE cursDatabases ;
END
IF @cursPermisTable_Status > -2
BEGIN
CLOSE cursPermisTable ;
DEALLOCATE cursPermisTable ;
END
IF @cursDNamesAsColumns_Status > -2
BEGIN
CLOSE cursDNamesAsColumns ;
DEALLOCATE cursDNamesAsColumns ;
END
SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;
END CATCH
GO
/*
EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
*/
CREATE PROCEDURE Get_permission
AS
DECLARE @db_name VARCHAR(200),
@sql_text VARCHAR(max)
SET @sql_text='Create table ##db_name (user_name varchar(max),'
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
OPEN db_cursor
FETCH next FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_text=@sql_text + @db_name + ' varchar(max),'
FETCH next FROM db_cursor INTO @db_name
END
CLOSE db_cursor
SET @sql_text=@sql_text + 'Server_perm varchar(max))'
EXEC (@sql_text)
DEALLOCATE db_cursor
DECLARE @RoleName VARCHAR(50)
DECLARE @UserName VARCHAR(50)
DECLARE @CMD VARCHAR(1000)
CREATE TABLE #permission
(
user_name VARCHAR(50),
databasename VARCHAR(50),
role VARCHAR(50)
)
DECLARE longspcur CURSOR FOR
SELECT name
FROM sys.server_principals
WHERE type IN ( 'S', 'U', 'G' )
AND principal_id > 4
AND name NOT LIKE '##%'
AND name <> 'NT AUTHORITY\SYSTEM'
AND name <> 'ONDEMAND\Administrator'
AND name NOT LIKE 'steel%'
OPEN longspcur
FETCH next FROM longspcur INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #userroles_kk
(
databasename VARCHAR(50),
role VARCHAR(50)
)
CREATE TABLE #rolemember_kk
(
dbrole VARCHAR(100),
membername VARCHAR(100),
membersid VARBINARY(2048)
)
SET @CMD = 'use ? truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = ''' + @UserName + ''''
EXEC Sp_msforeachdb
@CMD
INSERT INTO #permission
SELECT @UserName 'user',
b.name,
u.role
FROM sys.sysdatabases b
LEFT OUTER JOIN #userroles_kk u
ON u.databasename = b.name --and u.Role='db_owner'
ORDER BY 1
DROP TABLE #userroles_kk;
DROP TABLE #rolemember_kk;
FETCH next FROM longspcur INTO @UserName
END
CLOSE longspcur
DEALLOCATE longspcur
TRUNCATE TABLE ##db_name
DECLARE @d1 VARCHAR(max),
@d2 VARCHAR(max),
@d3 VARCHAR(max),
@ss VARCHAR(max)
DECLARE perm_cur CURSOR FOR
SELECT *
FROM #permission
ORDER BY 2 DESC
OPEN perm_cur
FETCH next FROM perm_cur INTO @d1, @d2, @d3
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT 1
FROM ##db_name
WHERE user_name = @d1)
BEGIN
SET @ss='insert into ##db_name(user_name) values ('''
+ @d1 + ''')'
EXEC (@ss)
SET @ss='update ##db_name set ' + @d2 + '=''' + @d3
+ ''' where user_name=''' + @d1 + ''''
EXEC (@ss)
END
ELSE
BEGIN
DECLARE @var NVARCHAR(max),
@ParmDefinition NVARCHAR(max),
@var1 NVARCHAR(max)
SET @var = N'select @var1=' + @d2
+ ' from ##db_name where USER_NAME=''' + @d1
+ '''';
SET @ParmDefinition = N'@var1 nvarchar(300) OUTPUT';
EXECUTE Sp_executesql
@var,
@ParmDefinition,
@var1=@var1 output;
SET @var1=Isnull(@var1, ' ')
SET @var= ' update ##db_name set ' + @d2 + '=''' + @var1 + ' '
+ @d3 + ''' where user_name=''' + @d1 + ''' '
EXEC (@var)
END
FETCH next FROM perm_cur INTO @d1, @d2, @d3
END
CLOSE perm_cur
DEALLOCATE perm_cur
SELECT *
FROM ##db_name
DROP TABLE ##db_name
DROP TABLE #permission
멋진 감사 대본 감사합니다.
감사 사용자에게는 멋진 Kenneth Fisher(b | t) 스토어드 프로시저를 사용하는 것을 강력히 추천합니다.
여러 데이터베이스/SQL 서버에서 실행하도록 요청하는 사용자에게 응답할 수 없습니다.
등록된 서버 그룹을 만들고 데이터베이스 전체에서 다음 커서를 조회합니다.
--Make sure all ' are doubled within the SQL string.
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM MASTER.dbo.sysdatabases
where name like '%DBName%'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';'+ '
/*
Security Audit Report
1) List all access provisioned to a SQL user or Windows user/group directly
2) List all access provisioned to a SQL user or Windows user/group through a database or application role
3) List all access provisioned to the public role
Columns Returned:
UserType : Value will be either ''SQL User'', ''Windows User'', or ''Windows Group''.
This reflects the type of user/group defined for the SQL Server account.
DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the
same as the server user.
LoginName : SQL or Windows/Active Directory user account. This could also be an Active Directory group.
Role : The role name. This will be null if the associated permissions to the object are defined at directly
on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE,
SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
Schema : Name of the schema the object is in.
ObjectName : Name of the object that the user/role is assigned permissions on.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value
is only populated if the object is a table, view or a table value function.
*/
--1) List all access provisioned to a SQL user or Windows user/group directly
SELECT
[UserType] = CASE princ.[type]
WHEN ''S'' THEN ''SQL User''
WHEN ''U'' THEN ''Windows User''
WHEN ''G'' THEN ''Windows Group''
END,
[DatabaseUserName] = princ.[name],
[LoginName] = ulogin.[name],
[Role] = NULL,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Database user
sys.database_principals AS princ
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid]
--Permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN (''S'',''U'',''G'')
-- No need for these system accounts
AND princ.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')
UNION
--2) List all access provisioned to a SQL user or Windows user/group through a database or application role
SELECT
[UserType] = CASE membprinc.[type]
WHEN ''S'' THEN ''SQL User''
WHEN ''U'' THEN ''Windows User''
WHEN ''G'' THEN ''Windows Group''
END,
[DatabaseUserName] = membprinc.[name],
[LoginName] = ulogin.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Role/member associations
sys.database_role_members AS members
--Roles
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
--Role members (database users)
JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid]
--Permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
membprinc.[type] IN (''S'',''U'',''G'')
-- No need for these system accounts
AND membprinc.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')
UNION
--3) List all access provisioned to the public role, which everyone gets by default
SELECT
[UserType] = ''{All Users}'',
[DatabaseUserName] = ''{All Users}'',
[LoginName] = ''{All Users}'',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Roles
sys.database_principals AS roleprinc
--Role permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
--All objects
JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
roleprinc.[type] = ''R''
AND roleprinc.[name] = ''public''
AND obj.[is_ms_shipped] = 0
ORDER BY
[UserType],
[DatabaseUserName],
[LoginName],
[Role],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType]
'
exec sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
이 실이 많은 도움이 되었습니다.여러분 감사합니다!
데이터베이스 db_dataareader에 할당된 역할이 해당 역할에 대한 권한을 표시하지 않았기 때문에 Jeremy의 답변에 다음과 같이 추가했습니다.저는 모든 게시물에 있는 모든 답변을 검토해보았지만, 이렇게 할 수 있는 것을 찾을 수 없어서 저만의 질문을 추가했습니다.
SELECT
UserType='Role',
DatabaseUserName = '{Role Members}',
LoginName = DP2.name,
Role = DP1.name,
'SELECT' AS [PermissionType] ,
[PermissionState] = 'GRANT',
[ObjectType] = 'Table',
[Schema] = 'dbo',
[ObjectName] = 'All Tables',
[ColumnName] = NULL
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'
AND DP2.name IS NOT NULL
이 모든 것을 시도해 보았지만, 특히 sysadmin 사용자가 누락된 것을 금방 알 수 있었습니다.그런 구멍을 뚫는 건 곧 있을 감사에서 좋지 않아 보여서 이렇게 생각해낸 거야
USE master
GO
SELECT DISTINCT
p.name AS [loginname] ,
--p.type,
p.type_desc ,
p.is_disabled,
s.sysadmin,
sp.permission_name
FROM sys.server_principals p
INNER JOIN sys.syslogins s ON p.sid = s.sid
INNER JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- Logins that are not process logins
AND p.name NOT LIKE '##%'
ORDER BY p.name
GO
다음은 Jeremy에 의해 제출된 가장 일반적인 답변입니다만, Greg Sipes에 의해 언급된 sysadmin 플래그와 disabled 플래그를 포함하여 log_date_time 컬럼을 포함하도록 수정되었습니다.
둘 다 최고라고?
/*
Source: https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role
Columns Returned:
UserName : SQL or Windows/Active Directory user account. This could also be an Active Directory group.
UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the
SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the
same as the server user.
Role : The role name. This will be null if the associated permissions to the object are defined at directly
on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE,
SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ObjectName : Name of the object that the user/role is assigned permissions on.
This value may not be populated for all roles. Some built in roles have implicit permission
definitions.
ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value
is only populated if the object is a table, view or a table value function.
*/
--List all access provisioned to a sql user or windows user/group directly
SELECT
[UserName] = CASE princ.[type]
WHEN 'S' THEN princ.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = princ.[name],
[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],
sp.is_disabled,
s.sysadmin,
GETDATE() AS [log_date_time]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.login_token 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]
LEFT JOIN sys.syslogins s ON princ.sid = s.sid
LEFT JOIN sys.server_principals sp ON princ.name = sp.name
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
[UserName] = CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = memberprinc.[name],
[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],
sp.is_disabled,
s.sysadmin,
GETDATE() AS [log_date_time]
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.login_token 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]
LEFT JOIN sys.syslogins s ON memberprinc.[sid] = s.sid
LEFT JOIN sys.server_principals sp ON memberprinc.[name] = sp.name
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[DatabaseUserName] = '{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],
sp.is_disabled,
s.sysadmin,
GETDATE() AS [log_date_time]
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]
LEFT JOIN sys.syslogins s ON roleprinc.sid = s.sid
LEFT JOIN sys.server_principals sp ON roleprinc.name = sp.name
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]
SysAdmin인지 아닌지만 보여주는 단순한 쿼리:
IF IS_SRVROLEMEMBER ('sysadmin') = 1
print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
print 'ERROR: The server role specified is not valid.';
아쉽게도 평판이 좋지 않아 Sean Rose 게시물에 코멘트를 할 수 없었지만, sys.objects에 대한 (INNER) JOIN으로 인해 SCHEMA 범위의 권한이 표시되지 않아 스크립트의 "public" 역할 부분을 수정해야 했습니다.LEFT JOIN으로 변경한 후 WHERE-Clause 로직을 수정하여 시스템 객체를 생략해야 했습니다.공공 파마를 위한 나의 수정된 질의는 아래와 같습니다.
--3) List all access provisioned to the public role, which everyone gets by default
SELECT
@@servername ServerName
, db_name() DatabaseName
, [UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[LoginName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Roles
sys.database_principals AS roleprinc
--Role permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
--All objects
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
roleprinc.[type] = 'R'
AND roleprinc.[name] = 'public'
AND isnull(obj.[is_ms_shipped], 0) = 0
AND isnull(object_schema_name(perm.[major_id]), '') <> 'sys'
ORDER BY
[UserType],
[DatabaseUserName],
[LoginName],
[Role],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType]
특정 로그인에 대한 데이터베이스에 대한 액세스를 확인하려면 다음 간단한 스크립트를 사용합니다.
sys.sp_helplogins @LoginNamePattern = '도메인\로그인' -- sysname
내가 기부를 할 차례가 왔다.
이 보고서 헤더는 보고서가 실행되는 SQL 인스턴스 이름, 날짜 및 계정 이름을 동적으로 가져옵니다.이 모든 것은 감사자가 알아야 할 사항입니다.:)
주의 - 마스터 데이터베이스에 '환경'이라는 확장 속성이 있는 경우 보고서 헤더에 값(PreProd, Development, Production, DR 등)이 포함됩니다.
BEGIN
BEGIN TRY
SET NOCOUNT ON
SELECT 'See Messages Tab..... use Ctrl+SHIFT+F and re-run to ''send to file'''
DECLARE @DBName nvarchar(2000) = DB_NAME()
DECLARE @User_Name nvarchar(200) = suser_sname()
DECLARE @Account_Name nvarchar(200)
DECLARE @Granted_permissions nvarchar(2000)
DECLARE @Permission_State nvarchar(200)
DECLARE @ParentObject nvarchar(200)
DECLARE @env2 varchar(50) = Convert(varchar(50),(Select ServerProperty('Servername')));
DECLARE @day varchar(50) = FORMAT (getdate(), 'dddd, MM, yyyy');
DECLARE @clk varchar(50) = FORMAT (getdate(), 'hh:mm:ss tt') ;
DECLARE @env1 VARCHAR(25) = (SELECT CAST(value AS varchar(25))
FROM [master].[sys].fn_listextendedproperty('environment', default, default, default, default, default, default));
PRINT '*** ' + @DBName + ' Security Audit Report ***';
PRINT ' in the ' + @env1 + ' environment';
PRINT ' on SQL Instance: ' + @env2;
PRINT ' '+ @day + ' at ' + @clk;
PRINT ' run under account ' + @User_Name;
PRINT ' '
CREATE TABLE #GP(
DBName NVARCHAR(200),
Account_Name NVARCHAR(200),
Granted_Permissions NVARCHAR(max),
Permission_State NVARCHAR(200),
ParentObject NVARCHAR(200)
)
;WITH SampleDataR AS
(SELECT
DB_NAME() AS 'DBName'
,dp.name AS 'Account_Name'
,dpm.permission_name AS 'Granted_Permissions'
,dpm.state_desc AS 'Permission_State'
,dpm.class_desc AS 'ParentObject'
, ROW_NUMBER() OVER (PARTITION BY DB_NAME(), dp.[name] ,dpm.state_desc, dpm.class_desc ORDER BY permission_name) rownum
FROM sys.database_principals dp
LEFT OUTER JOIN [sys].[database_permissions] dpm
ON dp.principal_id = dpm.grantee_principal_id
WHERE dp.type ='R'
AND dp.sid IS NOT NULL
AND dp.name <> 'public'
AND dp.name NOT LIKE 'db_a%'
AND dp.name NOT LIKE 'db_b%'
AND dp.name NOT LIKE 'db_d%'
AND dp.name NOT LIKE 'db_o%'
AND dp.name NOT LIKE 'db_s%'
--AND dpm.class_desc = 'DATABASE' -- remove to see schema based permissions
)
--Select * from SampleDataR
INSERT INTO #GP
SELECT DISTINCT
DBName
,Account_Name
,(SELECT Granted_Permissions +
CASE
WHEN s1.rownum = (select MAX(rownum)
FROM SampleDataR
WHERE DBName = s1.DBName AND
Account_Name = s1.Account_Name AND
ParentObject = s1.ParentObject)
THEN ' (' + Permission_State + '), '
ELSE ' (' + Permission_State + '), '
END
FROM SampleDataR s1
WHERE s1.DBName = s2.DBName AND
s1.Account_Name = s2.Account_Name AND
s1.ParentObject = s2.ParentObject
FOR xml path(''),type).value('(.)[1]','varchar(max)'
) Granted_Permissions
,Permission_State
,ParentObject
FROM SampleDataR s2
--Select * from #GP
PRINT 'Assigned Role Permissions'
PRINT ' '
SET NOCOUNT ON
DECLARE cur CURSOR FOR
SELECT DISTINCT DBName, Account_Name, ParentObject, Granted_permissions
FROM #GP
OPEN cur
SET NOCOUNT ON
FETCH NEXT FROM cur INTO @DBname, @Account_Name, @ParentObject, @Granted_permissions;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName + ', ' + @Account_Name + ', ' + '[' + @ParentObject + '], ' + @Granted_permissions
FETCH NEXT FROM cur INTO @DBname, @Account_Name, @ParentObject , @Granted_permissions;
END
CLOSE cur;
DEALLOCATE cur;
SET NOCOUNT ON
DROP Table #GP
SET NOCOUNT ON
DECLARE @DBName2 nvarchar(200)
DECLARE @Account_Name2 nvarchar(200)
DECLARE @Granted_permissions2 nvarchar(200)
CREATE TABLE #GP2(
DBName NVARCHAR(200),
Account_Name NVARCHAR(200) ,
Granted_Permissions NVARCHAR(200)
)
;WITH SampleDataR AS
(SELECT
DB_NAME() AS 'DBName'
,dp.name AS 'Account_Name'
--,dp.type
,dpm.permission_name
,ROW_NUMBER() OVER (PARTITION BY DB_NAME(), dp.[name] ORDER BY permission_name) rownum
FROM sys.database_principals dp
LEFT OUTER JOIN [sys].[database_permissions] dpm
ON dp.principal_id = dpm.grantee_principal_id
--order by dp.type
WHERE dp.type not in ('A', 'R', 'X') --removed 'G',
AND dp.sid is not null
AND dp.name not in ('guest','dbo')
)
INSERT INTO #GP2
SELECT DISTINCT
DBName
,Account_Name
,(SELECT permission_name +
CASE
WHEN s1.rownum = (select MAX(rownum)
FROM SampleDataR
WHERE DBName = s1.DBName and Account_Name = s1.Account_Name
)
THEN ''
ELSE ','
END
FROM SampleDataR s1
WHERE s1.DBName = s2.DBName AND s1.Account_Name = s2.Account_Name
FOR xml path(''),type).value('(.)[1]','varchar(max)') Granted_Permissions
FROM SampleDataR s2;
PRINT ' '
PRINT ' '
PRINT 'Assigned User Permissions'
PRINT ' '
DECLARE cur CURSOR FOR
SELECT DBName, Account_Name, Granted_permissions
FROM #GP2
OPEN cur
SET NOCOUNT ON
FETCH NEXT FROM cur INTO @DBname2, @Account_Name2, @Granted_permissions2;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName2 + ', ' + @Account_Name2 + ', ' + @Granted_permissions2
FETCH NEXT FROM cur INTO @DBname2, @Account_Name2, @Granted_permissions2;
END
CLOSE cur;
DEALLOCATE cur;
DROP TABLE #GP2
SET NOCOUNT ON
DECLARE @DBName3 nvarchar(200)
DECLARE @Role_Name3 nvarchar(max)
DECLARE @Members3 nvarchar(max)
CREATE TABLE #GP3(
DBName NVARCHAR(200),
Role_Name NVARCHAR(max),
members NVARCHAR(max)
)
;WITH SampleDataR AS
(SELECT
DB_NAME() AS 'DBName'
,r.name AS 'role_name'
,m.name AS 'members'
,ROW_NUMBER() OVER (PARTITION BY DB_NAME(), r.[name] ORDER BY m.[name]) rownum
FROM sys.database_role_members rm
INNER JOIN sys.database_principals r on rm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals m on rm.member_principal_id = m.principal_id
)
INSERT INTO #GP3
SELECT DISTINCT
DBName
,Role_Name
,(SELECT Members +
CASE
WHEN s3.rownum = (select MAX(rownum)
FROM SampleDataR
WHERE DBName = s3.DBName and Role_Name = s3.Role_Name
)
THEN ','
ELSE ','
END
FROM SampleDataR s1
WHERE s1.DBName = s3.DBName and s1.Role_Name = s3.Role_Name
FOR xml path(''),type).value('(.)[1]','varchar(max)') Members
FROM SampleDataR s3
PRINT ' '
PRINT ' '
PRINT 'Assigned Role Membership'
PRINT ' '
DECLARE cur CURSOR FOR
SELECT DBName, Role_Name, Members
FROM #GP3
OPEN cur
SET NOCOUNT ON
FETCH NEXT FROM cur INTO @DBname3, @Role_Name3, @Members3;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName3 + ', ' + @Role_Name3 + ', ' + @Members3
FETCH NEXT FROM cur INTO @DBname3, @Role_Name3, @Members3;
END
CLOSE cur;
DEALLOCATE cur;
DROP Table #GP3;
END TRY
BEGIN CATCH
SELECT 'Real ERROR at Line #' + CAST(ERROR_LINE() AS VARCHAR(20));
-- Throw/raise and error caught from the Try section.
THROW;
END CATCH;
끝.
--스토어드 프로시저로서 보존하기 좋은
언급URL : https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database
'programing' 카테고리의 다른 글
| UILabel - 텍스트에 맞게 레이블 크기를 자동 조정하시겠습니까? (0) | 2023.04.12 |
|---|---|
| Common Table Expression(CTE; 공통 테이블 표현)을 사용하는 경우 (0) | 2023.04.07 |
| SQL: WHERE 절 내의 IF 절 (0) | 2023.04.07 |
| DateTime을 VarChar로 변환하는 방법 (0) | 2023.04.07 |
| SQL Server에서 소수점 이하 자리 잘라내기(둥글지 않음) (0) | 2023.04.07 |