User Permissions in all databases in a SQL Server

Audit is a regular activity for databases. Sometime internal, sometimes external, sometimes bi-yearly, sometimes yearly, sometimes for preparation for external audit, sometimes corrections to existing audits and so on. Doing it manually is quite time-consuming and ofcourse, boring due to repetitive nature of activities. I prefer to automate generation of portions of audit information as much as possible. For database audits, mostly it is about logins, what access they have, permissions by groups, etc. So I thought of creating a table with columns that would contain the data/information that auditors generally seek and a simple SP that would populate this table. The SP would traverse user databases to get the information. Usually DBA's have a database specific to high end DBA activities like Service broker, job progression, etc. Let us call it my DBADB for this article's sake. This kind of table can reside in such a database where interference is minimal. So let us start with the table, dbo.DB_Permissions_By_Group some column names of which are self explanatory:

USE [DBADB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF;
go
CREATE TABLE [dbo].[DB_Permissions_By_Group](
 [DatabaseName] [varchar](32) NOT NULL,
 [Class_Desc] [varchar](128) NULL,
 [ObjectName] [varchar](64) NULL,
 [Minor_Id] [smallint] NULL,
 [Type] [varchar](24) NULL,
 [Permission_Name] [varchar](24) NULL,
 [State_Desc] [varchar](16) NULL,
 [GroupName] [varchar](64) NULL
) ON [PRIMARY]
with (data_compression = page)
GO

The other columns that need some description are following:
[Class_Desc] is type of objects which can be a database, schema, table, SP, etc.
[Type] is type of permission. For e.g. Execute, Select, Update, View, Showplan, etc.
[GroupName] is a Windows AD group that has some permission in some application database on this server. There can be one or more individual logins or groups within a given Windows group. And these members will have the same permission as the parent Windows group that they belong to.

Now to the SP that would populate this table, [dbo].[DB_Permissions_By_Group]. The SP is called 
[dbo].[dbp_Show_Permissions]. This SP scans all the non-system databases and fetches the permission on a given object for each user that exists in that database. Here is the code of this SP:

USE [DBADB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create or alter procedure [dbo].[dbp_Show_Permissions]
as
set nocount on
declare @l_exec_String varchar(8000)
       ,@l_db_name     varchar(32)
truncate table SBMDB.dbo.DB_Permissions_By_Group
declare DB_Cursor scroll cursor
for
select name from sys.databases
where name not in ('master', 'model', 'msdb', 'tempdb', 'SSISDB')
open DB_Cursor
fetch next from DB_Cursor into
     @l_db_name
while (@@FETCH_STATUS = 0)
begin
 select @l_exec_String = 'Use ' + @l_db_name + '; select db_name() DatabaseName, a.Class_Desc, case when a.class_desc = ' + '''' + 'Schema' + ''''
        + ' then  schema_name(a.major_id) else object_name(a.major_id) end ObjectName '
        + ',a.minor_id, a.[Type], a.[permission_name], a.state_desc ,b.name from sys.database_permissions a '
        + 'inner join sys.database_Principals b '
        + 'on a.grantee_principal_id = b.principal_id '
        + 'where b.name like ' + '''%MyDomain\%'''
 insert into [DBADB].dbo.DB_Permissions_By_Group (DatabaseName, Class_Desc, ObjectName, Minor_Id, [Type], [Permission_Name], State_Desc, GroupName)
 exec (@l_exec_String)
    fetch next from DB_Cursor into
            @l_db_name
end
close DB_Cursor
deallocate DB_Cursor
set nocount off;
GO
Explanation: Basically, it joins the DMV's sys.database_permissions (for permissions) and sys.database_principals (for users) and matches them based on the principal_id which is the ID of the database user for a given login. It does it for each user database and puts the result in our table,  [DBADB].dbo.DB_Permissions_By_Group.

Since it sifts through only the DMV's in each user database, performance is not a concern. It takes less than a few seconds to go through a few dozen databases. Once this SP gets executed, the user needs to simply extract the contents of  the table, [DBADB].dbo.DB_Permissions_By_Group into an Excel file and send it to auditors.


Execution: execute [DBADB].[dbo].[dbp_Show_Permissions]

Results:      Select * from [DBADB].dbo.DB_Permissions_By_Group with (nolock)