In SQL Server you can use the sp_column_privileges_ex system stored procedure to return column privileges for the columns from a specified linked server.
You can specify an individual column, or you can specify all columns from a given database, table, etc.
Syntax
The syntax goes like this:
sp_column_privileges_ex [ @table_server = ] 'table_server'
[ , [ @table_name = ] 'table_name' ]
[ , [ @table_schema = ] 'table_schema' ]
[ , [ @table_catalog = ] 'table_catalog' ]
[ , [ @column_name = ] 'column_name' ]
The @table_server argument is the only required argument. This is the name of the linked server that you want the table information from.
The other arguments are optional, and I cover them in the following examples. For more information on these arguments, see the Microsoft documentation.
Example 1 – Return Privileges for a Specific Column
The following example returns privileges for a specific column, in a specific table, from a specific table schema, in a specific database.
EXEC sp_column_privileges_ex @table_server = 'Homer', @table_name = 'City', @table_schema = 'Dimension', @table_catalog = 'WideWorldImportersDW', @column_name = 'Region';
Result:
+----------------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | GRANTOR | GRANTEE | PRIVILEGE | IS_GRANTABLE | |----------------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------| | WideWorldImportersDW | Dimension | City | Region | dbo | dbo | INSERT | YES | | WideWorldImportersDW | Dimension | City | Region | dbo | dbo | REFERENCES | YES | | WideWorldImportersDW | Dimension | City | Region | dbo | dbo | SELECT | YES | | WideWorldImportersDW | Dimension | City | Region | dbo | dbo | UPDATE | YES | +----------------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+
This could also be executed like this:
EXEC sp_column_privileges_ex 'Homer', 'City', 'Dimension', 'WideWorldImportersDW', 'Region';
Example 2 – Using the Default Database
If you don’t provide the @table_catalog argument (to specify the database), the default database for the linked server will be used.
If I remove the @table_catalog argument from the previous example:
EXEC sp_column_privileges_ex @table_server = 'Homer', @table_name = 'City', @table_schema = 'Dimension', @column_name = 'Region';
I get the following result:
(0 rows affected) Time: 0.321s
This is because the WideWorldImportersDW database is not the default database for the linked server. In this case, when I created the linked server, I used @catalog = 'Music' to specify that the database called Music would be the default database for this linked server.
So if I specify a column that happens to be in the default database, I’ll get results:
EXEC sp_column_privileges_ex @table_server = 'Homer', @table_name = 'Artists', @table_schema = 'dbo', @column_name = 'ArtistId';
I get the following result:
+-------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | GRANTOR | GRANTEE | PRIVILEGE | IS_GRANTABLE | |-------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------| | 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 | +-------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+
Example 3 – Specify a Table Only
In this example I only specify the table name.
EXEC sp_column_privileges_ex @table_server = 'Homer', @table_name = 'Artists';
Result:
+-------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+ | TABLE_CAT | TABLE_SCHEM | 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 | | Music | dbo | Artists | CountryId | dbo | dbo | INSERT | YES | | Music | dbo | Artists | CountryId | dbo | dbo | REFERENCES | YES | | Music | dbo | Artists | CountryId | dbo | dbo | SELECT | YES | | Music | dbo | Artists | CountryId | dbo | dbo | UPDATE | YES | +-------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+
This returns the privileges for all columns in the specified table.
Example 4 – Specify the Column Name Only
In this example I specify the column name only (also the server, obviously).
EXEC sp_column_privileges_ex @table_server = 'Homer', @column_name = 'ArtistName';
Results:
+-------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | GRANTOR | GRANTEE | PRIVILEGE | IS_GRANTABLE | |-------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------| | 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 | | Music | dbo | BluesAlbums | ArtistName | dbo | dbo | INSERT | YES | | Music | dbo | BluesAlbums | ArtistName | dbo | dbo | REFERENCES | YES | | Music | dbo | BluesAlbums | ArtistName | dbo | dbo | SELECT | YES | | Music | dbo | BluesAlbums | ArtistName | dbo | dbo | UPDATE | YES | | Music | dbo | JazzAlbums | ArtistName | dbo | dbo | INSERT | YES | | Music | dbo | JazzAlbums | ArtistName | dbo | dbo | REFERENCES | YES | | Music | dbo | JazzAlbums | ArtistName | dbo | dbo | SELECT | YES | | Music | dbo | JazzAlbums | ArtistName | dbo | dbo | UPDATE | YES | | Music | dbo | RockAlbums | ArtistName | dbo | dbo | INSERT | YES | | Music | dbo | RockAlbums | ArtistName | dbo | dbo | REFERENCES | YES | | Music | dbo | RockAlbums | ArtistName | dbo | dbo | SELECT | YES | | Music | dbo | RockAlbums | ArtistName | dbo | dbo | UPDATE | YES | +-------------+---------------+--------------+---------------+-----------+-----------+-------------+----------------+
This returned privileges for three more columns. These happen to be from three different views (the database has three views with an ArtistName column: BluesAlbums, JazzAlbums, and RockAlbums).
Example 5 – Specify Only the Linked Server (no other arguments)
Here I only specify the linked server – I don’t provide any other arguments. In this case, it will return all privileges for all columns in the database:
EXEC sp_column_privileges_ex @table_server = 'Homer';
I won’t display the results here because it returned over 26,000 rows. Most of these were from the sys table schema.
Example 6 – Specify a Table Schema
The following example narrows the results to a specific table schema (dbo).
EXEC sp_column_privileges_ex @table_server = 'Homer', @table_schema = 'dbo';
This returned a much smaller result set than the previous example. It’s still quite large, so I won’t display it here.
Example 7 – Wildcard Characters
You can also use wildcard characters. Here’s an example of using the % wildcard character:
EXEC sp_column_privileges_ex @table_server = 'Homer', @table_schema = 'dbo', @table_catalog = 'Music', @column_name = 'Ar%';
This returns privileges for all columns that begin with Ar.