INFORMATION SCHEMA TABLE_PRIVILEGES
– Identifies privileges granted to or by the current user
CREATE VIEW INFORMATION_SCHEMA.TABLE_PRIVILEGES
AS
SELECT
user_name(p.grantor_principal_id) AS GRANTOR,
user_name(p.grantee_principal_id) AS GRANTEE,
db_name() AS TABLE_CATALOG,
schema_name(o.schema_id) AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
convert(varchar(10),
CASE p.type
WHEN ‘RF’ THEN ‘REFERENCES’
WHEN ‘SL’ THEN ‘SELECT’
WHEN ‘IN’ THEN ‘INSERT’
WHEN ‘DL’ THEN ‘DELETE’
WHEN ‘UP’ THEN ‘UPDATE’
END) AS PRIVILEGE_TYPE,
convert(varchar(3),
CASE p.state
WHEN ‘G’ THEN ‘NO’
WHEN ‘W’ THEN ‘YES’
END) AS IS_GRANTABLE
FROM
sys.objects o,
sys.database_permissions p
WHERE
o.type IN (’U', ‘V’)
AND p.class = 1
AND p.major_id = o.object_id
AND p.minor_id = 0 — all columns
AND p.type IN (’RF’,'IN’,'SL’,'UP’,'DL’)
AND p.state IN (’W',’G')
AND NOT EXISTS(SELECT * FROM sys.database_permissions m
WHERE m.class = 1
AND m.major_id = p.major_id
AND m.grantee_principal_id = p.grantee_principal_id
AND m.grantor_principal_id = p.grantor_principal_id
AND m.type = p.type
AND m.state IN (’R',’D'))
AND 0 <> (permissions(o.object_id) & — back compat
CASE p.type
WHEN ‘RF’ THEN 4 — REFERENCES ON all columns
WHEN ‘SL’ THEN 1 — SELECT ON all columns
WHEN ‘IN’ THEN 8 — INSERT
WHEN ‘DL’ THEN 16 — DELETE
WHEN ‘UP’ THEN 2 — UPDATE ON all columns
END)
———-