INFORMATION_SCHEMA.COLUMN_PRIVILEGES

The next script is the Code behind of the INFORMATION_SCHEMA.COLUMN_PRIVILEGES view.

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
	AND p.type IN (
		'RF'
		,'SL'
		,'UP'
		)
	AND p.STATE IN (
		'G'
		,'W'
		)
	AND (
		p.grantee_principal_id = 0
		OR p.grantee_principal_id = DATABASE_PRINCIPAL_ID()
		OR p.grantor_principal_id = DATABASE_PRINCIPAL_ID()
		)
Advertisements
This entry was posted in Database Administration, Information Schema. Bookmark the permalink.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s