Remo SQL Formatter

(under construction)

Unformatted SQL


Formatted SQL

SELECT CASE p.CLASS WHEN 0 THEN p.class_desc WHEN 3 THEN p.class_desc ELSE o.type_desc END AS [objectType], CASE p.CLASS WHEN 0 THEN '' WHEN 3 THEN '' ELSE SCHEMA_NAME(o.SCHEMA_ID) END AS [objectSchema], CASE p.CLASS WHEN 3 THEN SCHEMA_NAME(p.major_id) WHEN 1 THEN OBJECT_NAME(p.major_id) ELSE '' END AS [objectName], ISNULL(c.name, '') AS [columnName], USER_NAME(p.grantee_principal_id) AS [user], p.permission_name AS [permission], p.state_desc AS [permissionState], o.is_ms_shipped [MS], CONVERT(VARCHAR(64), NULL) AS [checksum]
INTO #tmp
FROM sys.database_permissions p
INNER JOIN sys.all_objects o ON p.major_id = o.OBJECT_ID
LEFT OUTER JOIN sys.all_columns C ON p.major_id = c.OBJECT_ID
  AND p.minor_id = c.column_id
RIGHT OUTER JOIN sys.extended_properties ep ON o.OBJECT_ID = ep.major_id
  AND ep.name = 'microsoft_database_tools_support'
WHERE USER_NAME(p.grantee_principal_id) IN ('ADMIN', 'READER')
ORDER BY 1, 2, 3, 4, 6, 7, 5