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 | +-------------+---------------+--------------+-----------+-----------+-------------+----------------+