Chapter 6: Using Stored Procedures

sp_table_privileges

Description

Returns privilege information for one or more database objects.

Syntax

sp_table_privileges table_name [, table_owner] [, table_qualifier]

Parameters

table_name is the name of the table. Use the wildcard character to request information about more than one table. Aliases are not supported.

table_owner is the owner of the database object about which column privilege information is requested. Use the wildcard character to request information about tables owned by more than one user. If you do not specify this parameter, sp_table_privileges looks first for a table owned by the current user and then for a table owned by the database owner.

table_qualifier is ignored. Leave blank or set to NULL.

Usage

The server selects information from the SYSTABAUTH system catalog table.
This function corresponds to the ODBC function SQLTablePrivileges.

Results

sp_table privileges returns a list of one or more database objects with privilege information about each. Results are ordered by the following columns:

TABLE_OWNER
TABLE_NAME
PRIVILEGE

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 for sp_table_privileges.

**Table 6-19: Result set for sp_table_privileges**
Column Name	Datatype	Notes
TABLE_QUALIFIER	varchar (128)	Always NULL
TABLE_OWNER	varchar (128)	Table owner identifier (authorization ID)
TABLE_NAME	varchar (128) NOT NULL	Name of the database object about which privilege information is returned
GRANTOR	varchar (128)	Identifies the user who granted this privilege; NULL if not applicable to the target database
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 object as one of the following values: SELECT – the grantee is authorized to select rows in the associated object. INSERT – the grantee is authorized to insert rows into the associated object. UPDATE – the grantee is authorized to update rows in the associated object. REFERENCES – the grantee is authorized to refer to one or more columns of the table within a constraint (for example: unique, referential, or table check constraint).
IS_GRANTABLE	varchar (3)	Indicates whether the grantee is authorized to grant privilege on this object to other users with one of the following values: YES – the grantee can grant this privilege to others. NO – the grantee cannot grant this privilege to others. NULL – it is unknown or not applicable to the target database.

View this book as PDF