sp_table_privileges Catalog Stored Procedures

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

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

Syntax

sp_table_privileges table_name [, table_owner] [, table_qualifier]

where

table_name: Specifies the table used to return catalog information. The table_name can be a variable character length name with a maximum of 32 characters. Wildcard pattern matching is supported.
table_owner: Specifies the table owner of the table used to return catalog information. The table_owner can be a variable character name with a maximum of 32 characters. Wildcard pattern matching is supported. If the table_owner is not specified, the default table visibility rules of the underlying 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.

Remarks

SQL Server does not support the ability to grant privileges through SELECT_GRANTABLE, INSERT_GRANTABLE, UPDATE_GRANTABLE, DELETE_GRANTABLE, and REFERENCES_GRANTABLE.

The sp_table_privileges stored procedure is equivalent to SQLTablePrivileges in ODBC. The results returned are ordered by TABLE_QUALIFIER, TABLE_OWNER, TABLE_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.
GRANTOR	varchar(32)	Is the name of the database user who has granted permissions on this TABLE_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 TABLE_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 table privileges. Table 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 one or more of the columns. INSERT The GRANTEE can provide data for new rows for one or more of the columns. UPDATE The GRANTEE can modify existing data for one or more of the columns. DELETE The GRANTEE can remove rows from the table. 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. The scope of action given to the GRANTEE by a given table privilege is data-source dependent. For example, the UPDATE privilege might permit the GRANTEE to update all columns in a table on one data source and only those columns for which the GRANTOR has UPDATE privilege on another data source.
IS_GRANTABLE	varchar(3)	Indicates whether or not 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.