In SQL Server you can use the sp_column_privileges system stored procedure to return column privilege information for a given table in the current environment.

Provide the table name as an argument when executing the stored procedure, and the column privileges will be returned for that table. You can also supply the table owner, table qualifier, and/or the column name if required.

Syntax

The syntax goes like this:

sp_column_privileges [ @table_name = ] 'table_name'   
     [ , [ @table_owner = ] 'table_owner' ]   
     [ , [ @table_qualifier = ] 'table_qualifier' ]   
     [ , [ @column_name = ] 'column' ]

The @table_name argument is the only required argument. This is the name of the table that you want the column privileges from.

Example 1 – Return Privileges for a Specific Column

This example uses all possible arguments. It returns privileges for a specific column, in a specific table, from a specific table owner, in a specific database.

EXEC sp_column_privileges 
  @table_name = 'Cities', 
  @table_owner = 'Application',   
  @table_qualifier = 'WideWorldImporters',   
  @column_name = 'Location';

Result:

+--------------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+
| TABLE_QUALIFIER    | TABLE_OWNER   | TABLE_NAME   | COLUMN_NAME   | GRANTOR   | GRANTEE   | PRIVILEGE   | IS_GRANTABLE   |
|--------------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------|
| WideWorldImporters | Application   | Cities       | Location      | dbo       | dbo       | INSERT      | YES            |
| WideWorldImporters | Application   | Cities       | Location      | dbo       | dbo       | REFERENCES  | YES            |
| WideWorldImporters | Application   | Cities       | Location      | dbo       | dbo       | SELECT      | YES            |
| WideWorldImporters | Application   | Cities       | Location      | dbo       | dbo       | UPDATE      | YES            |
+--------------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+

Here’s a more concise way of doing it:

sp_column_privileges 
  'Cities', 
  'Application',   
  'WideWorldImporters',   
  'Location';

That returns the same results.

Example 2 – Specify a Table Only

In this example I switch to another database and only specify the table name.

USE Music;
EXEC sp_column_privileges
  @table_name = 'Artists';

Result:

+-------------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+
| TABLE_QUALIFIER   | TABLE_OWNER   | TABLE_NAME   | COLUMN_NAME   | GRANTOR   | GRANTEE   | PRIVILEGE   | IS_GRANTABLE   |
|-------------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------|
| Music             | dbo           | Artists      | ActiveFrom    | dbo       | dbo       | INSERT      | YES            |
| Music             | dbo           | Artists      | ActiveFrom    | dbo       | dbo       | REFERENCES  | YES            |
| Music             | dbo           | Artists      | ActiveFrom    | dbo       | dbo       | SELECT      | YES            |
| Music             | dbo           | Artists      | ActiveFrom    | dbo       | dbo       | UPDATE      | YES            |
| Music             | dbo           | Artists      | ArtistId      | dbo       | dbo       | INSERT      | YES            |
| Music             | dbo           | Artists      | ArtistId      | dbo       | dbo       | REFERENCES  | YES            |
| Music             | dbo           | Artists      | ArtistId      | dbo       | dbo       | SELECT      | YES            |
| Music             | dbo           | Artists      | ArtistId      | dbo       | dbo       | UPDATE      | YES            |
| Music             | dbo           | Artists      | ArtistName    | dbo       | dbo       | INSERT      | YES            |
| Music             | dbo           | Artists      | ArtistName    | dbo       | dbo       | REFERENCES  | YES            |
| Music             | dbo           | Artists      | ArtistName    | dbo       | dbo       | SELECT      | YES            |
| Music             | dbo           | Artists      | ArtistName    | dbo       | dbo       | UPDATE      | YES            |
+-------------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+

This returns the privileges for all columns in the specified table.

Note that you do need to be in the correct database. If I re-run the previous example on a different database, I get no results.

USE WideWorldImporters;
EXEC sp_column_privileges
  @table_name = 'Artists';

Result:

(0 rows affected)

Example 3 – About the Table Qualifier

If you provide the @table_qualifier argument, it must be the same as the current database. If it’s not, you’ll probably get error Msg 15250.

USE Music;
EXEC sp_column_privileges
  @table_name = 'Artists',
  @table_qualifier = 'WideWorldImporters';

Results:

Msg 15250, Level 16, State 1, Procedure sp_column_privileges, Line 19
The database name component of the object qualifier must be the name of the current database.