sp_column_privileges Catalog Stored Procedure

For SQL Server 6.5 information, see sp_column_privileges in What's New for SQL Server 6.5.

Returns column privilege information for a single table in the current environment.

Syntax

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

where

table_name: Specifies the table used to return catalog information. The table_name can be a variable-length character name with a maximum of 32 characters. Wildcard pattern matching is not supported.
table_owner: Specifies the table owner of the table used to return catalog information. The table_owner can be a variable-length character name with a maximum 32 characters. Wildcard pattern matching is not supported. If the table_owner is not specified, the default table visibility rules of the underlying database management system (DBMS) apply.
In Microsoft SQL Server, if the current user owns a table with the specified name, that table's columns are returned. If the table_owner is not specified and the current user does not own a table with the specified table_name, this procedure will look for a table with the specified table_name owned by the database owner. If one exists, that table's columns are returned.
table_qualifier: Is the name of the table qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.
column_name: Specifies a single column and is used when only one column of catalog information is desired. If column_name is not specified, all columns will be returned. In Microsoft SQL Server, this column_name represents the column name as listed in the syscolumns table. The value specified can include wildcard characters using the underlying DBMS's wildcard matching patterns. For maximum interoperability, the gateway client should assume only ANSI-standard SQL pattern matching (the % and _ wildcard characters).

Remarks

With SQL Server, privileges are given with the GRANT statement and taken away by the REVOKE statement.

The sp_column_privileges catalog stored procedure is equivalent to SQLColumnPrivileges in ODBC. The results returned are order by TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, and PRIVILEGE.

This is the results set:



Column	Datatype	Description

TABLE_QUALIFIER	varchar(32)	Is the name of the table qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment. This field can be NULL.
TABLE_OWNER	varchar(32)	Is the name of the table owner. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the name of the database user who created the table. This field will always return a value.
TABLE_NAME	varchar(32)	Is the name of the table. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the table name as listed in the sysobjects table. This field will always return a value.
COLUMN_NAME	varchar(32)	Is the name of the column, for each column of the TABLE_NAME returned. In Microsoft SQL Server, this column represents the column name as listed in the syscolumns table. This field will always return a value.
GRANTOR	varchar(32)	Is the name of the database user who has granted permissions on this COLUMN_NAME to the listed GRANTEE. In Microsoft SQL Server, this column will always be the same as the TABLE_OWNER. This field will always return a value.
GRANTEE	varchar(32)	Is the name of the database user who has been granted permissions on this COLUMN_NAME by the listed GRANTOR. In Microsoft SQL Server, this column will always include a database user from the sysusers table. This field will always return a value.
PRIVILEGE	varchar(32)	Is one of the available column privileges. Column privileges can be one of the following values (or other values supported by the data source when implementation is defined): SELECT The GRANTEE can retrieve data for the columns. INSERT The GRANTEE can provide data for this column when new rows are inserted (by the GRANTEE) into the table. UPDATE The GRANTEE can modify existing data in the column. REFERENCES The GRANTEE can reference a column in a foreign table in a primary key/foreign key relationship. In SQL Server 6.0, primary key/foreign key relationships are defined with table constraints.
IS_GRANTABLE	varchar(3)	Indicates whether the GRANTEE is permitted to grant privileges to other users (often referred to as "grant with grant" permission). Can be YES, NO, or NULL. An unknown (or NULL) value refers to a data source where "grant with grant" is not applicable. In SQL Server 6.0, "grant with grant" permission is not supported.