title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_table_privileges_ex (Transact-SQL) |
Returns privilege information about the specified table from the specified linked server. |
markingmyname |
maghan |
randolphwest |
12/28/2023 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE SQL Server]
Returns privilege information about the specified table from the specified linked server.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_table_privileges_ex
[ @table_server = ] N'table_server'
[ , [ @table_name = ] N'table_name' ]
[ , [ @table_schema = ] N'table_schema' ]
[ , [ @table_catalog = ] N'table_catalog' ]
[ , [ @fUsePattern = ] fUsePattern ]
[ ; ]
The name of the linked server for which to return information. @table_server is sysname, with no default.
The name of the table for which to provide table privilege information. @table_name is sysname, with a default of NULL
.
The table schema. This in some DBMS environments is the table owner. @table_schema is sysname, with a default of NULL
.
The name of the database in which the specified @table_name resides. @table_catalog is sysname, with a default of NULL
.
Determines whether the characters _
, %
, [
, and ]
are interpreted as wildcard characters. Valid values are 0
(pattern matching is off) and 1
(pattern matching is on). @fUsePattern is bit, with a default of 1
.
None.
Column name | Data type | Description |
---|---|---|
TABLE_CAT |
sysname | Table qualifier name. Various DBMS products support three-part naming for tables (<qualifier>.<owner>.<name> ). In [!INCLUDE ssNoVersion], 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_SCHEM |
sysname | Table owner name. In [!INCLUDE ssNoVersion], this column represents the name of the database user who created the table. This field always returns a value. |
TABLE_NAME |
sysname | Table name. This field always returns a value. |
GRANTOR |
sysname | Database username that's granted permissions on this TABLE_NAME to the listed GRANTEE . In [!INCLUDE ssNoVersion], this column is always the same as the TABLE_OWNER . This field always returns a value. Also, the GRANTOR column might 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. |
GRANTEE |
sysname | Database username that's been granted permissions on this TABLE_NAME by the listed GRANTOR . This field always returns a value. |
PRIVILEGE |
varchar(32) | 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 [!INCLUDE ssNoVersion], primary key/foreign key relationships are defined by using table constraints.The scope of action given to the GRANTEE by a specific table privilege is data source-dependent. For example, the UPDATE permission could enable the GRANTEE to update all columns in a table on one data source and only those columns for which the GRANTOR has UPDATE permission on another data source. |
IS_GRANTABLE |
varchar(3) | Indicates whether the GRANTEE is permitted to grant permissions to other users. This is often referred to as "grant with grant" permission. Can be YES , NO , or NULL . An unknown, or NULL , value refers to a data source in which "grant with grant" isn't applicable. |
The results returned are ordered by TABLE_QUALIFIER
, TABLE_OWNER
, TABLE_NAME
, and PRIVILEGE
.
Requires SELECT
permission on the schema.
The following example returns privilege information about tables with names that start with Product
in the [!INCLUDE ssSampleDBobject] database from the specified linked server Seattle1
. [!INCLUDE ssNoVersion] is assumed as the linked server.
EXEC sp_table_privileges_ex @table_server = 'Seattle1',
@table_name = 'Product%',
@table_schema = 'Production',
@table_catalog ='AdventureWorks2022';