Chapter 11: Accessing Catalog Information with CSPs

sp_column_privileges

Returns column privilege information for a single database object.

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

table_name: is the name of the table. Wildcard-character search patterns are not supported. Aliases are not supported. Views are supported but do not include alter or index privileges.

table_owner: is the name of the table owner. Wildcard-character search patterns are not supported.

column_name: is the name of the column for which you want privilege information. Use wildcard-character search patterns to request information about more than one column. Leave blank or set to NULL to request information about all columns in the table or tables.

This function corresponds to the ODBC function SQLColumnPrivileges.
Information is based on the SYSCOLAUTH, SYSCOLUMNS, and SYSTABAUTH system catalog tables.

sp_column_privileges returns one row for each privilege a user has on a column in a table. Results are ordered by the following columns:

The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.

The following table shows the result set.

**Table 11-3: Result set for sp_column_privileges**
Column name	Datatype	Description
TABLE_QUALIFIER	varchar (128)	Always NULL
TABLE_OWNER	varchar (128)	Authorization ID
TABLE_NAME	varchar (128) NOT NULL	Name of the object about which privilege information is returned
COLUMN_NAME	varchar (128) NOT NULL	Column name
GRANTOR	varchar (128)	Identifies the user who granted this privilege
GRANTEE	varchar (128) NOT NULL	Identifies the user to whom this privilege was granted
PRIVILEGE	varchar (128) NOT NULL	Identifies the privilege granted to the grantee on this column as one of the following values: SELECT if the grantee is authorized to select rows in the associated object UPDATE if the grantee is authorized to insert and update rows in the associated object
IS_GRANTABLE	varchar (3)	Indicates whether the grantee is authorized to grant privilege on this column to other users; always NULL