Returns a list of table permissions (such as INSERT, DELETE, UPDATE, SELECT, REFERENCES) for the specified table or tables.
Table qualifier name. In SQL Server, this column represents the database name. This field can be NULL.
Table owner name. This field always returns a value.
Table name. This field always returns a value.
Database username that has granted permissions on this TABLE_NAME to the listed GRANTEE. In SQL Server, this column is always the same as the TABLE_OWNER. This field always returns a value. Also, the GRANTOR column may be either the database owner (TABLE_OWNER) or a user to whom the database owner granted permission by using the WITH GRANT OPTION clause in the GRANT statement.
Database username that has been granted permissions on this TABLE_NAME by the listed GRANTOR. In SQL Server, this column always includes a database user from the sys.database_principals system view. This field always returns a value.
One of the available table permissions. Table 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 one or more of the columns.
INSERT = GRANTEE can provide data for new rows for one or more of the columns.
UPDATE = GRANTEE can modify existing data for one or more of the columns.
DELETE = GRANTEE can remove rows from the table.
REFERENCES = GRANTEE can reference a column in a foreign table in a primary key/foreign key relationship. In SQL Server, 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 may 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.
Indicates whether or not 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.
The following example returns privilege information about all tables with names beginning with the word Contact.
USE AdventureWorks2008R2; GO EXEC sp_table_privileges @table_name = 'Contact%';