title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_column_privileges_ex (Transact-SQL) |
sp_column_privileges_ex returns column privileges for the specified table on the specified linked server. |
markingmyname |
maghan |
randolphwest |
07/05/2024 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE SQL Server]
Returns column privileges for the specified table on the specified linked server.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_column_privileges_ex
[ @table_server = ] N'table_server'
[ , [ @table_name = ] N'table_name' ]
[ , [ @table_schema = ] N'table_schema' ]
[ , [ @table_catalog = ] N'table_catalog' ]
[ , [ @column_name = ] N'column_name' ]
[ ; ]
The name of the linked server for which to return information. @table_server is sysname, with no default.
The name of the table that contains the specified column. @table_name is sysname, with a default of NULL
.
The table schema. @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
.
The name of the column for which to provide privilege information. @column_name is sysname, with a default of NULL
(all common).
The following table shows the result set columns. The results returned are ordered by TABLE_QUALIFIER
, TABLE_OWNER
, TABLE_NAME
, COLUMN_NAME
, and PRIVILEGE
.
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. |
COLUMN_NAME |
sysname | Column name, for each column of the TABLE_NAME returned. This field always returns a value. |
GRANTOR |
sysname | Database user name that was granted permissions on this COLUMN_NAME to the listed GRANTEE . In [!INCLUDE ssNoVersion], 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 someone to whom the database owner granted permissions by using the WITH GRANT OPTION clause in the GRANT statement. |
GRANTEE |
sysname | Database user name that was granted permissions on this COLUMN_NAME by the listed GRANTOR . 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 where "grant with grant" isn't applicable. |
Requires SELECT
permission on the schema.
The following example returns column privilege information for the HumanResources.Department
table in the [!INCLUDE ssSampleDBobject] database on the Seattle1
linked server.
EXEC sp_column_privileges_ex
@table_server = 'Seattle1',
@table_name = 'Department',
@table_schema = 'HumanResources',
@table_catalog = 'AdventureWorks2022';