Find All Public Role Permissions

A standard best-practice in SQL Server is that the public role should not have any permissions. Here is a script that will show you all permissions granted to the public role in all databases on an instance.

-- Find all permissions in all databases granted to the public role
SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#DBList') IS NOT NULL
    DROP TABLE #DBList
IF OBJECT_ID('tempdb..#ObjectList') IS NOT NULL
    DROP TABLE #ObjectList

CREATE TABLE #DBList (
    EntryID         int             IDENTITY,
    DbName          sysname
)

CREATE TABLE #ObjectList (
    EntryID         int             IDENTITY    PRIMARY KEY,
    DatabaseName    nvarchar(128),
    ObjectType      nvarchar(60),
    ObjectName      sysname,
    Permission      nvarchar(128),
    GrantState      nvarchar(60)
)

DECLARE @dbCt           int
DECLARE @objCt          int
DECLARE @dbName         nvarchar(128)
DECLARE @sql            nvarchar(4000)

INSERT INTO #DBList (
    DbName
)
SELECT name
FROM sys.databases
ORDER BY name

SET @dbCt = 0
WHILE @dbCt < (SELECT COUNT(*) FROM #DBList) BEGIN
    SET @dbCt = @dbCt + 1
    SELECT @dbName = DbName
    FROM #DBList
    WHERE EntryID = @dbCt

    SET @sql =
        N'USE ' + QUOTENAME(@dbName) + ';
        INSERT INTO #ObjectList (
            DatabaseName,
            ObjectType,
            ObjectName,
            Permission,
            GrantState
        )
        SELECT
            DB_NAME(),
            o.type_desc,
            o.name,
            dp.permission_name,
            dp.state_desc
        FROM sys.database_permissions   AS dp
        JOIN sys.objects                AS o  ON dp.major_id = o.[object_id]
        WHERE 1 = 1
          AND dp.grantee_principal_id = (
            SELECT principal_id
            FROM sys.database_principals
            WHERE name = ''public''
          )
          AND dp.[state] IN (''G'', ''W'')
          AND o.is_ms_shipped = 0
          AND o.name NOT LIKE ''MSmerge%''
          AND o.name NOT LIKE ''sysmerge%''
          AND o.name NOT IN (
            ''MSsubscription_properties''
          )
        ORDER BY
            o.type_desc,
            o.name,
            dp.permission_name'

    EXEC sp_executesql
        @stmt           = @sql,
        @params         = N'@dbNameParam nvarchar(128)',
        @dbNameParam    = @dbName;
END

SELECT
    ROW_NUMBER() OVER (
        PARTITION BY DatabaseName
        ORDER BY EntryID
    ) AS [DB Row],
    DatabaseName,
    ObjectType,
    ObjectName,
    Permission,
    GrantState
FROM #ObjectList
ORDER BY
    EntryID
GO