sp_column_privileges

Description

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 whose permissions 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

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 to refer to the column within a constraint (for example, a unique, referential, or table check 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.