Return Table Privileges from a Linked Server in SQL Server (T-SQL Examples)

In SQL Server you can use the sp_table_privileges_ex system stored procedure to return privilege information about a specified table from a specified linked server.

You can specify an individual table, or you can specify all tables from a given database or table schema. You can also use wildcard characters to specify the table/s. However, you can also specify whether the wildcard characters should be interpreted as wildcard characters.

Syntax

The syntax goes like this:

sp_table_privileges_ex [ @table_server = ] 'table_server'   
     [ , [ @table_name = ] 'table_name' ]   
     [ , [ @table_schema = ] 'table_schema' ]   
     [ , [ @table_catalog = ] 'table_catalog' ]  
     [ , [@fUsePattern =] 'fUsePattern']

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 they’re demonstrated in the following examples.

Example 1 – Return Privileges for a Specific Table

This example returns privileges for a specific column, in a specific table, from a specific table schema, in a specific database.

EXEC sp_table_privileges_ex 
  @table_server = 'Homer',
  @table_name = 'City', 
  @table_schema = 'Dimension', 
  @table_catalog = 'WideWorldImportersDW';

Result:

+----------------------+---------------+--------------+-----------+-----------+-------------+----------------+
| TABLE_CAT            | TABLE_SCHEM   | TABLE_NAME   | GRANTOR   | GRANTEE   | PRIVILEGE   | IS_GRANTABLE   |
|----------------------+---------------+--------------+-----------+-----------+-------------+----------------|
| WideWorldImportersDW | Dimension     | City         | dbo       | dbo       | DELETE      | YES            |
| WideWorldImportersDW | Dimension     | City         | dbo       | dbo       | INSERT      | YES            |
| WideWorldImportersDW | Dimension     | City         | dbo       | dbo       | REFERENCES  | YES            |
| WideWorldImportersDW | Dimension     | City         | dbo       | dbo       | SELECT      | YES            |
| WideWorldImportersDW | Dimension     | City         | dbo       | dbo       | UPDATE      | YES            |
+----------------------+---------------+--------------+-----------+-----------+-------------+----------------+

This could also be executed like this:

EXEC sp_table_privileges_ex 
  'Homer',
  'City',
  'Dimension',
  'WideWorldImportersDW';

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_table_privileges_ex 
  @table_server = 'Homer',
  @table_name = 'City', 
  @table_schema = 'Dimension';

I get the following result:

(0 rows affected)
Time: 0.311s

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_table_privileges_ex 
  @table_server = 'Homer',
  @table_name = 'Artists';

I get the following result:

+-------------+---------------+--------------+-----------+-----------+-------------+----------------+
| TABLE_CAT   | TABLE_SCHEM   | TABLE_NAME   | GRANTOR   | GRANTEE   | PRIVILEGE   | IS_GRANTABLE   |
|-------------+---------------+--------------+-----------+-----------+-------------+----------------|
| Music       | dbo           | Artists      | dbo       | dbo       | DELETE      | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | INSERT      | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | REFERENCES  | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | SELECT      | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | UPDATE      | YES            |
+-------------+---------------+--------------+-----------+-----------+-------------+----------------+

Notice that I didn’t specify the table schema in this example, as it’s optional.

Example 3 – Specify the Table Schema Name Only

In this example I specify the schema name only (also the server, obviously).

EXEC sp_table_privileges_ex 
  @table_server = 'Homer',
  @table_schema = 'dbo';

That returns 35 rows on my system. This covers 4 tables and 3 views.

Example 4 – 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_table_privileges_ex 
  @table_server = 'Homer';

I won’t display the results here because it returned over 3,000 rows. Most of these were from the sys table schema.

Example 5 – Wildcard Characters

You can also use wildcard characters. Here’s an example of using the % wildcard character:

EXEC sp_table_privileges_ex 
  @table_server = 'Homer',    
  @table_name = 'Ar%';

Result:

+-------------+---------------+--------------+-----------+-----------+-------------+----------------+
| TABLE_CAT   | TABLE_SCHEM   | TABLE_NAME   | GRANTOR   | GRANTEE   | PRIVILEGE   | IS_GRANTABLE   |
|-------------+---------------+--------------+-----------+-----------+-------------+----------------|
| Music       | dbo           | Artists      | dbo       | dbo       | DELETE      | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | INSERT      | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | REFERENCES  | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | SELECT      | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | UPDATE      | YES            |
+-------------+---------------+--------------+-----------+-----------+-------------+----------------+

This returns privileges for all tables that begin with Ar.

However, you can also use the @fUsePattern argument to determine whether or not wildcard characters should be interpreted as wildcard characters. The default value is 1, which means that they should be interpreted as wildcard characters. A value of 0 specifies that they should not be interpreted as wildcard characters.

Here’s what happens if I add @fUsePattern = 0 to the previous example:

EXEC sp_table_privileges_ex 
  @table_server = 'Homer',    
  @table_name = 'Ar%',
  @fUsePattern = 0;

Result:

(0 rows affected)
Time: 0.318s

And here’s what happens if I set it to 1:

EXEC sp_table_privileges_ex 
  @table_server = 'Homer',    
  @table_name = 'Ar%',
  @fUsePattern = 1;

Result:

+-------------+---------------+--------------+-----------+-----------+-------------+----------------+
| TABLE_CAT   | TABLE_SCHEM   | TABLE_NAME   | GRANTOR   | GRANTEE   | PRIVILEGE   | IS_GRANTABLE   |
|-------------+---------------+--------------+-----------+-----------+-------------+----------------|
| Music       | dbo           | Artists      | dbo       | dbo       | DELETE      | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | INSERT      | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | REFERENCES  | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | SELECT      | YES            |
| Music       | dbo           | Artists      | dbo       | dbo       | UPDATE      | YES            |
+-------------+---------------+--------------+-----------+-----------+-------------+----------------+