Returns column privilege information for a single table in the current environment.
sp_column_privileges [@table_name =] 'table_name'
[,[@table_owner =] 'table_owner']
[,[@table_qualifier =] 'table_qualifier']
[,[@column_name =] 'column']
In Microsoft® SQL Server™, if the current user owns a table with the specified name, that table’s columns are returned. If table_owner is not specified and the current user does not own a table with the specified table_name, sp_column privileges looks for a table with the specified table_name owned by the database owner. If one exists, that table’s columns are returned.
None
sp_column_privileges is equivalent to SQLColumnPrivileges in ODBC. The results returned are ordered by TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, and PRIVILEGE.
Column name | Data type | Description |
---|---|---|
TABLE_QUALIFIER | sysname | Table qualifier name. This field can be NULL. |
TABLE_OWNER | sysname | Table owner name. This field always returns a value. |
TABLE_NAME | sysname | Table name. This field always returns a value. |
COLUMN_NAME | sysname | Column name, for each column of the TABLE_NAME returned. This field always returns a value. |
GRANTOR | sysname | Database username that has granted permissions on this COLUMN_NAME to the listed GRANTEE. In SQL Server, this column is always the same as the TABLE_OWNER. This field always returns a value.
The GRANTOR column can be either the database owner (TABLE_OWNER) or a user to whom the database owner granted permissions by using the WITH GRANT OPTION clause in the GRANT statement. |
GRANTEE | sysname | Database username that has been granted permissions on this COLUMN_NAME by the listed GRANTOR. In SQL Server, this column always includes a database user from the sysusers table. This field always returns a value. |
PRIVILEGE | varchar(32) | One of the available column permissions. Column permissions can be one of the following values (or other values supported by the data source when implementation is defined):
SELECT = GRANTEE can retrieve data for the columns. INSERT = GRANTEE can provide data for this column when new rows are inserted (by the GRANTEE) into the table. UPDATE = GRANTEE can modify existing data in the column. REFERENCES = GRANTEE can reference a column in a foreign table in a primary key/foreign key relationship. Primary key/foreign key relationships are defined with table constraints. |
IS_GRANTABLE | varchar(3) | Indicates whether the GRANTEE is permitted to grant permissions 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 for which “grant with grant” is not applicable. |
With SQL Server, permissions are given with the GRANT statement and taken away by the REVOKE statement.
Execute permission defaults to public role.
This example returns column privilege information for a table.
EXEC sp_column_privileges Employee
Distributed Queries | REVOKE |
GRANT | System Stored Procedures |