USE [?]
GO
SELECT
DB_ID()
AS
[db_id],
DB_NAME()
AS
[db_name],
DPER.[class_desc],
DPER.[permission_name],
DPER.[state_desc],
DPRI.[
name
],
DPRI.[type_desc],
CASE
WHEN
DPER.[class_desc] =
'DATABASE'
THEN
DB_NAME()
WHEN
DPER.[class_desc] =
'OBJECT_OR_COLUMN'
AND
DPER.[minor_id] = 0
THEN
SCHEMA_NAME(O.[schema_id]) +
'.'
+OBJECT_NAME(DPER.[major_id])
WHEN
DPER.[class_desc] =
'OBJECT_OR_COLUMN'
AND
DPER.[minor_id] != 0
THEN
SCHEMA_NAME(O.[schema_id]) +
'.'
+ OBJECT_NAME(DPER.[major_id]) +
'('
+ C.[
name
] +
')'
WHEN
DPER.[class_desc] =
'SCHEMA'
THEN
SCHEMA_NAME(DPER.[major_id])
WHEN
DPER.[class_desc] =
'DATABASE_PRINCIPAL'
THEN
(
SELECT
[
name
]
FROM
sys.database_principals
WHERE
[principal_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'ASSEMBLY'
THEN
(
SELECT
[clr_name]
COLLATE
DATABASE_DEFAULT
FROM
sys.assemblies
WHERE
[assembly_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'TYPE'
THEN
TYPE_NAME(DPER.[major_id])
WHEN
DPER.[class_desc] =
'XML_SCHEMA_COLLECTION'
THEN
(
SELECT
[
name
]
FROM
sys.xml_schema_collections
WHERE
[xml_collection_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'MESSAGE_TYPE'
THEN
(
SELECT
[
name
]
FROM
sys.service_message_types
WHERE
[message_type_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'SERVICE_CONTRACT'
THEN
(
SELECT
[
name
]
FROM
sys.service_contracts
WHERE
[service_contract_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'SERVICE'
THEN
(
SELECT
[
name
]
FROM
sys.services
WHERE
[service_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'REMOTE_SERVICE_BINDING'
THEN
(
SELECT
[
name
]
FROM
sys.remote_service_bindings
WHERE
[remote_service_binding_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'ROUTE'
THEN
(
SELECT
[
name
]
FROM
sys.routes
WHERE
[route_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'FULLTEXT_CATALOG'
THEN
(
SELECT
[
name
]
FROM
sys.fulltext_catalogs
WHERE
[fulltext_catalog_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'SYMMETRIC_KEY'
THEN
(
SELECT
[
name
]
FROM
sys.symmetric_keys
WHERE
[symmetric_key_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'CERTIFICATE'
THEN
(
SELECT
[
name
]
FROM
sys.certificates
WHERE
[certificate_id] = DPER.[major_id])
WHEN
DPER.[class_desc] =
'ASYMMETRIC_KEY'
THEN
(
SELECT
[
name
]
FROM
sys.asymmetric_keys
WHERE
[asymmetric_key_id] = DPER.[major_id])
ELSE
OBJECT_NAME(DPER.[major_id])
END
AS
[object_name],
GR.[
name
]
AS
[grantor_principal]
FROM
sys.database_permissions DPER
JOIN
sys.database_principals DPRI
ON
DPER.[grantee_principal_id] = DPRI.[principal_id]
JOIN
sys.database_principals GR
ON
DPER.[grantor_principal_id] = GR.[principal_id]
LEFT
OUTER
JOIN
sys.objects O
ON
DPER.[major_id] = O.[object_id]
LEFT
OUTER
JOIN
sys.columns C
ON
DPER.[major_id] = C.[object_id]
AND
DPER.[minor_id] = C.[column_id]
WHERE
[DPER].[major_id] > -1