INFORMATION SCHEMA. COLUMN PRIVILEGES
– Identifies privileges granted to or by current user
CREATE VIEW INFORMATION_SCHEMA.COLUMN_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,
c.name AS COLUMN_NAME,
convert(varchar(10),
CASE p.type
WHEN ‘SL’ THEN ‘SELECT’
WHEN ‘UP’ THEN ‘UPDATE’
WHEN ‘RF’ THEN ‘REFERENCES’
END) AS PRIVILEGE_TYPE,
convert(varchar(3),
CASE p.state
WHEN ‘G’ THEN ‘NO’
WHEN ‘W’ THEN ‘YES’
END) AS IS_GRANTABLE
FROM
sys.database_permissions p,
sys.objects o,
sys.columns c
WHERE
o.type IN (’U', ‘V’)
AND o.object_id = c.object_id
AND p.class = 1
AND p.major_id = o.object_id
AND (p.minor_id = c.column_id or
(p.minor_id = 0 AND
NOT EXISTS (SELECT * FROM sys.database_permissions m
WHERE m.class = 1 AND
m.major_id = p.major_id AND
m.minor_id = c.column_id AND
m.type = p.type AND
m.state <> p.state)))
AND p.type IN (’RF’,'SL’,'UP’)
AND p.state IN (’G', ‘W’)
AND 0 != (permissions(o.object_id, c.name) & — back compat
CASE p.type
WHEN ‘RF’ THEN 4 — REFERENCES basebit
WHEN ‘SL’ THEN 1 — SELECT basebit
WHEN ‘UP’ THEN 2 — UPDATE basebit
END)
————