Execute Sp_table_privileges on all SQL Server instance databases

Requesting sp_table_privileges across all databases on a SQL Server Instance is common during audit requests.  DBAs and auditors interrogate the output and  identify any security risks such as the is_grantable security risk.

Excecuting  sp_table_privileges  on a  database returns the privileges recordset  based on parameters set. For example, executing sp_table_privileges ‘%’  will return privileges for all tables on the current database.

Executing this query for all databases , outputting to a file and concatenating the files can be time consuming, and prone to error. Fortunately there is a method within SQL Server to execute sp_table_privileges across all the databases using the exec sp_msforeachdb stored procedure.

exec sp_msforeachdb 'USE [?]; exec sp_table_privileges ''%'''

Executing this code in SSMS  generates the table level privileges across the databases, but generates multiple recordsets i.e 1 for each database.  Therefore querying across all the databases is awkward. A workaround is to place in a temporary table.

CREATE TABLE #tmp1
(
table_qualifier sysname, 
table_owner sysname, 
table_name sysname, 
grantor sysname, 
grantee sysname, 
privilege sysname, 
is_grantable sysname 
) 
INSERT INTO #tmp1
exec sp_msforeachdb 'USE [?]; exec sp_table_privileges ''%'''

SELECT * FROM #tmp1
DROP TABLE #tmp1

Read More on security audits

How to create a SQL Server Security Audit - SQL Server DBA

Powershell - run script on all sql servers - SQL Server DBA

SQL Server - Powershell and Failed Logon attempts - SQL Server DBA