sp_column_privileges

Returns permissions information for one or more columns in a table or view.

Syntax

sp_column_privileges table_name [, table_owner
	[, table_qualifier [, column_name]]]

Parameters

table_name – is the name of the table. The use of wildcard characters in pattern matching is not supported.
table_owner – is the name of the table owner. The use of wildcard characters in pattern matching is not supported. If you do not specify the table’s owner, sp_column_privileges looks for a table owned by the current user and then for a table owned by the Database Owner.
table_qualifier – is the name of the database. Values are the name of the current database and null.
column_name – is the name of the column with permissions that you want to display. Use wildcard characters to request information for more than one column. If you do not specify a column name, permissions information for all columns in the specified table is returned.

Examples

Example 1 – This example displays information about the discounts table:

sp_column_privileges discounts, null, null, discounttype

table_qualifier     table_owner    table_name          column_name 
     grantor   grantee     privilege     is_grantable 
------------------- -------------- ------------------- ----------------
     --------- ----------- ------------- ------------ 
pubs2               dbo            discounts           discounttype 
     dbo       dbo          SELECT       YES 
pubs2               dbo            discounts           discounttype 
     dbo       dbo          UPDATE       YES 
pubs2               dbo            discounts           discounttype 
     dbo       dbo          REFERENCE   YES 
pubs2               dbo            discounts           discounttype 
     dbo       guest       SELECT        NO 
pubs2               dbo            discounts           discounttype 
     dbo       guest       UPDATE        NO 
pubs2               dbo            discounts           discounttype 
     dbo       guest       REFERENCE     NO

Usage

The results set for sp_column_privileges is:

Column	Datatype	Description
`table_qualifier`	`varchar(32)`	The name of the database in which the table specified for the `table_name` parameter is stored.
`table_owner`	`varchar(32)`	The table owner. If no value was specified for the `table_owner` parameter, this value is the current owner or the Database Owner.
`table_name`	`varchar(32)`	The name specified for the `table_name` parameter. This value cannot be NULL.
`column_name`	`varchar(32)`	The specified column name. If no column name was specified in the statement, the results include all columns in the specified table.
`grantor`	`varchar(32)`	The name of the database user who has granted permissions on `column_name` to `grantee`. This value cannot be NULL.
`grantee`	`varchar(32)`	The name of the database user who was granted permissions on `column_name` by `grantor`. This value cannot be NULL.
`privilege`	`varchar(32)`	Identifies the column privilege. May be one of the following: SELECT – The grantee is permitted to retrieve data for the column. UPDATE – The grantee is permitted to update data in the column. REFERENCE – The grantee is permitted only for referential constraint.
`is_grantable`	`varchar(3)`	Indicates whether the grantee is permitted to grant the privilege to other users. The values are YES, NO, and NULL.

Permissions

Any user can execute sp_column_privileges.