SQL Server query to find all permissions/access fo

Question

I would like to write a query on a sql 2008 that will report all the users that have access to a specific database, or objects within the database such as tables, views, and stored procedures, either directly or due to roles, etc. This report would be used for security auditing purposes. Not sure if anyone has a query that will fit my needs completely, but hopefully something that will give me a good start. Either sql 2008, 2005 or 2000 will do, I can probably convert as needed.

Answer 1

The GetPermissions Stored Procedure above is good however it uses Sp_msforeachdb which means that it will break if your SQL Instance has any databases names that include spaces or dashes and other non-best-practices characters. I have created a version that avoids the use of Sp_msforeachdb and also includes two columns that indicate 1 - if the Login is a sysadmin login (IsSysAdminLogin) and 2 - if the login is an orphan user (IsEmptyRow).

USE [master] ;
GO
IF EXISTS
(
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns')
    AND [type] in (N'P',N'PC')
)
BEGIN
    DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
END
GO
CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns
AS
SET NOCOUNT ON
;
BEGIN TRY
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[#permission]')
    )
    DROP TABLE #permission
    ;
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]')
    )
    DROP TABLE #userroles_kk
    ;
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]')
    )
    DROP TABLE #rolemember_kk
    ;
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[##db_name]')
    )
    DROP TABLE ##db_name
    ;
    DECLARE
    @db_name VARCHAR(255)
    ,@sql_text VARCHAR(MAX) 
    ;
    SET @sql_text =
    'CREATE TABLE ##db_name
    (
        LoginUserName VARCHAR(MAX)
        ,' 
    ;
    DECLARE cursDBs CURSOR FOR 
        SELECT [name]
        FROM sys.databases
        ORDER BY [name]
    ;
    OPEN cursDBs 
    ;
    FETCH NEXT FROM cursDBs INTO @db_name 
    WHILE @@FETCH_STATUS = 0 
        BEGIN 
                SET @sql_text =
        @sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX)
        ,' 
                FETCH NEXT FROM cursDBs INTO @db_name 
        END 
    CLOSE cursDBs 
    ;
    SET @sql_text =
        @sql_text + 'IsSysAdminLogin CHAR(1)
        ,IsEmptyRow CHAR(1)
    )' 

    --PRINT @sql_text
    EXEC (@sql_text)
    ;
    DEALLOCATE cursDBs 
    ;
    DECLARE
    @RoleName VARCHAR(255) 
    ,@UserName VARCHAR(255) 
    ;
    CREATE TABLE #permission 
    (
     LoginUserName VARCHAR(255)
     ,databasename VARCHAR(255)
     ,[role] VARCHAR(255)
    ) 
    ;
    DECLARE cursSysSrvPrinName CURSOR FOR 
        SELECT [name]
        FROM sys.server_principals 
        WHERE
        [type] IN ( 'S', 'U', 'G' )
        AND principal_id > 4
        AND [name] NOT LIKE '##%'
        ORDER BY [name]
    ;
    OPEN cursSysSrvPrinName
    ;
    FETCH NEXT FROM cursSysSrvPrinName INTO @UserName 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        CREATE TABLE #userroles_kk 
        ( 
             databasename VARCHAR(255)
             ,[role] VARCHAR(255)
        ) 
        ;
        CREATE TABLE #rolemember_kk 
        ( 
             dbrole VARCHAR(255)
             ,membername VARCHAR(255)
             ,membersid VARBINARY(2048)
        ) 
        ;
        DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR
        SELECT [name]
        FROM sys.databases
        ORDER BY [name]
        ;
        OPEN cursDatabases
        ;
        DECLARE 
        @DBN VARCHAR(255)
        ,@sqlText NVARCHAR(4000)
        ;
        FETCH NEXT FROM cursDatabases INTO @DBN
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sqlText =
    N'USE ' + QUOTENAME(@DBN) + ';
    TRUNCATE TABLE #RoleMember_kk 
    INSERT INTO #RoleMember_kk 
    EXEC sp_helprolemember 
    INSERT INTO #UserRoles_kk
    (DatabaseName,[Role])
    SELECT db_name(),dbRole
    FROM #RoleMember_kk
    WHERE MemberName = ''' + @UserName + '''
    '

            --PRINT @sqlText ;
            EXEC sp_executesql @sqlText ;
        FETCH NEXT FROM cursDatabases INTO @DBN
        END
        CLOSE cursDatabases
        ;
        DEALLOCATE cursDatabases
        ;
        INSERT INTO #permission 
        SELECT
        @UserName 'user'
        ,b.name
        ,u.[role]
        FROM
        sys.sysdatabases b
        LEFT JOIN
        #userroles_kk u 
            ON QUOTENAME(u.databasename) = QUOTENAME(b.name)
        ORDER  BY 1 
        ;
        DROP TABLE #userroles_kk
        ; 
        DROP TABLE #rolemember_kk
        ;
        FETCH NEXT FROM cursSysSrvPrinName INTO @UserName 
    END 
    CLOSE cursSysSrvPrinName 
    ;
    DEALLOCATE cursSysSrvPrinName 
    ;
    TRUNCATE TABLE ##db_name 
    ;
    DECLARE
    @d1 VARCHAR(MAX)
    ,@d2 VARCHAR(MAX)
    ,@d3 VARCHAR(MAX)
    ,@ss VARCHAR(MAX)
    ;
    DECLARE cursPermisTable CURSOR FOR
        SELECT * FROM #permission 
        ORDER BY 2 DESC 
    ;
    OPEN cursPermisTable
    ;
    FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        IF NOT EXISTS
        (
            SELECT 1 FROM ##db_name WHERE LoginUserName = @d1
        )
        BEGIN 
            SET @ss =
            'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')' 
            EXEC (@ss) 
            ;
            SET @ss =
            'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + '''' 
            EXEC (@ss)
            ;
        END 
        ELSE 
        BEGIN 
            DECLARE
            @var NVARCHAR(MAX)
            ,@ParmDefinition NVARCHAR(MAX)
            ,@var1 NVARCHAR(MAX)
            ;
            SET @var =
            N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + ''''
            ; 
            SET @ParmDefinition =
            N'@var1 NVARCHAR(600) OUTPUT '
            ; 
            EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT
            ;
            SET @var1 =
            ISNULL(@var1, ' ')
            ;
            SET @var =
            '  UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + '''  '
            ;
            EXEC (@var)
            ;
        END
        FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
    END 
    CLOSE cursPermisTable
    ;
    DEALLOCATE cursPermisTable 
    ;
    UPDATE ##db_name SET
    IsSysAdminLogin = 'Y'
    FROM
    ##db_name TT
    INNER JOIN
    dbo.syslogins SL
        ON TT.LoginUserName = SL.[name]
    WHERE
    SL.sysadmin = 1
    ;
    DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR
    SELECT [name]
    FROM tempdb.sys.columns
    WHERE
    OBJECT_ID = OBJECT_ID('tempdb..##db_name')
    AND [name] NOT IN ('LoginUserName','IsEmptyRow')
    ORDER BY [name]
    ;
    OPEN cursDNamesAsColumns
    ;
    DECLARE 
    @ColN VARCHAR(255)
    ,@tSQLText NVARCHAR(4000)
    ;
    FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @tSQLText =
N'UPDATE ##db_name SET
IsEmptyRow = ''N''
WHERE IsEmptyRow IS NULL
AND ' + QUOTENAME(@ColN) + ' IS NOT NULL
;
'

        --PRINT @tSQLText ;
        EXEC sp_executesql @tSQLText ;
    FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
    END
    CLOSE cursDNamesAsColumns
    ;
    DEALLOCATE cursDNamesAsColumns
    ;
    UPDATE ##db_name SET
    IsEmptyRow = 'Y'
    WHERE IsEmptyRow IS NULL
    ;
    UPDATE ##db_name SET
    IsSysAdminLogin = 'N'
    FROM
    ##db_name TT
    INNER JOIN
    dbo.syslogins SL
        ON TT.LoginUserName = SL.[name]
    WHERE
    SL.sysadmin = 0
    ;
    SELECT * FROM ##db_name
    ;
    DROP TABLE ##db_name
    ;
    DROP TABLE #permission
    ;
END TRY
BEGIN CATCH
    DECLARE
    @cursDBs_Status INT
    ,@cursSysSrvPrinName_Status INT
    ,@cursDatabases_Status INT
    ,@cursPermisTable_Status INT
    ,@cursDNamesAsColumns_Status INT
    ;
    SELECT
    @cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs')
    ,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName')
    ,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases')
    ,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
    ,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
    ;
    IF @cursDBs_Status > -2
        BEGIN
            CLOSE cursDBs ;
            DEALLOCATE cursDBs ;
        END
    IF @cursSysSrvPrinName_Status > -2
        BEGIN
            CLOSE cursSysSrvPrinName ;
            DEALLOCATE cursSysSrvPrinName ;
        END
    IF @cursDatabases_Status > -2
        BEGIN
            CLOSE cursDatabases ;
            DEALLOCATE cursDatabases ;
        END
    IF @cursPermisTable_Status > -2
        BEGIN
            CLOSE cursPermisTable ;
            DEALLOCATE cursPermisTable ;
        END
    IF @cursDNamesAsColumns_Status > -2
        BEGIN
            CLOSE cursDNamesAsColumns ;
            DEALLOCATE cursDNamesAsColumns ;
        END
    SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;
END CATCH
GO
/*
EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
*/

Answer 2

Can't comment on accepted answer so I'll add some comments here:

• I second Brad on schemas issue. From MS reference sys.objects table contains only schema-scoped objects. So to get info about "higher level" objects (i.e. schemas in our case) you need to use sys.schemas table.
• For [ObjectType] it's better to use obj.type_desc only for OBJECT_OR_COLUMN permission class. For all other cases use perm.[class_desc]
• Another type of permission which is not handled so well with this query is IMPERSONATE. To get info about impersonations one should LEFT JOIN with sys.database_principals on perm.major_id = imp.principal_id
• With my experience it's better to replace sys.login_token with sys.server_principals as it will show also SQL Logins, not only Windows ones
• One should add 'G' to allowed principal types to allow Windows groups
• Also, one can exclude users sys and INFORMATION_SCHEMA from resulting table, as these users are used only for service

I'll post first piece of script with all proposed fixes, other parts should be changed as well:

SELECT  
    [UserName] = ulogin.[name],
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'G' THEN 'Windows Group'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = CASE perm.[class] 
                        WHEN 1 THEN obj.type_desc               -- Schema-contained objects
                        ELSE perm.[class_desc]                  -- Higher-level objects
                   END,       
    [ObjectName] = CASE perm.[class] 
                        WHEN 1 THEN OBJECT_NAME(perm.major_id)  -- General objects
                        WHEN 3 THEN schem.[name]                -- Schemas
                        WHEN 4 THEN imp.[name]                  -- Impersonations
                   END,
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.server_principals ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN
    sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
    sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE 
    princ.[type] IN ('S','U','G') AND
    -- No need for these system accounts
    princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

Answer 3

Awesome script Jeremy and contributors! Thanks!

I have a s-ton of users, so running this for all users was a nightmare. I couldn't add comments, so I am posting the whole script with the changes. I added a variable + where clause so I can search for anything matching up to 5 characters in the user name (or all users when left blank). Nothing special, but I thought it would be helpful in some use cases.

DECLARE @p_userName NVARCHAR(5) = 'UName' -- Specify up to five characters here (or none for all users)

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an            Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT, DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. 

*/

DECLARE @userName NVARCHAR(4) = @p_UserName + '%'
--List all access provisioned to a sql user or windows user/group directly 

SELECT  
[UserName] = CASE princ.[type] 
                WHEN 'S' THEN princ.[name]
                WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
             END,
[UserType] = CASE princ.[type]
                WHEN 'S' THEN 'SQL User'
                WHEN 'U' THEN 'Windows User'
             END,  
[DatabaseUserName] = princ.[name],       
[Role] = null,      
[PermissionType] = perm.[permission_name],       
[PermissionState] = perm.[state_desc],       
[ObjectType] = obj.type_desc,--perm.[class_desc],       
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM    
--database user
sys.database_principals princ  
LEFT JOIN
--Login accounts
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id 
                AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
princ.[type] in ('S','U')  
AND princ.[name] LIKE @userName  -- Added this line --CSLAGLE
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
[UserName] = CASE memberprinc.[type] 
                WHEN 'S' THEN memberprinc.[name]
                WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
             END,
[UserType] = CASE memberprinc.[type]
                WHEN 'S' THEN 'SQL User'
                WHEN 'U' THEN 'Windows User'
             END, 
[DatabaseUserName] = memberprinc.[name],   
[Role] = roleprinc.[name],      
[PermissionType] = perm.[permission_name],       
[PermissionState] = perm.[state_desc],       
[ObjectType] = obj.type_desc,--perm.[class_desc],   
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM    
--Role/member associations
sys.database_role_members members
JOIN
--Roles
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
--Role members (database users)
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
--Login accounts
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id 
                AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE memberprinc.[name] LIKE @userName -- Added this line --CSLAGLE
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
[UserName] = '{All Users}', 
[UserType] = '{All Users}', 
[DatabaseUserName] = '{All Users}',       
[Role] = roleprinc.[name],      
[PermissionType] = perm.[permission_name],       
[PermissionState] = perm.[state_desc],       
[ObjectType] = obj.type_desc,--perm.[class_desc],  
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM    
--Roles
sys.database_principals roleprinc
LEFT JOIN        
--Role permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id 
                AND col.[column_id] = perm.[minor_id]                   
JOIN 
--All objects   
sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
--Only roles
roleprinc.[type] = 'R' AND
--Only public role
roleprinc.[name] = 'public' AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY
princ.[Name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.[permission_name],
perm.[state_desc],
obj.type_desc--perm.[class_desc]  

Answer 4

This is my first crack at a query, based on Andomar's suggestions. This query is intended to provide a list of permissions that a user has either applied directly to the user account, or through roles that the user has.

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on.  
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.                 
*/

--List all access provisioned to a sql user or windows user/group directly 
SELECT  
    [UserName] = CASE princ.[type] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],       
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[type] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],   
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{All Users}',       
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]                   
JOIN 
    --All objects   
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc] 

Answer 5

Here is my version, adapted from others. I spent 30 minutes just now trying to remember how I came up with this, and @Jeremy 's answer seems to be the core inspiration. I didn't want to update Jeremy's answer, just in case I introduced bugs, so I am posting my version of it here.

/*


--Script source found at :  http://stackoverflow.com/a/7059579/1387418
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role



Columns Returned:
UserName         : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
UserType         : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                  SQL Server user account.
PrinciaplUserName: if UserName is not blank, then UserName else DatabaseUserName
PrincipalType    : Possible values are 'SQL User', 'Windows User', 'Database Role', 'Windows Group'
DatabaseUserName : Name of the associated user as defined in the database user account.  The database user may not be the
                   same as the server user.
Role             : The role name.  This will be null if the associated permissions to the object are defined at directly
                   on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType   : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                   DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                   This value may not be populated for all roles.  Some built in roles have implicit permission
                   definitions.
PermissionState  : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                   This value may not be populated for all roles.  Some built in roles have implicit permission
                   definitions.
ObjectType       : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                   SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                   This value may not be populated for all roles.  Some built in roles have implicit permission
                   definitions.          
ObjectName       : Name of the object that the user/role is assigned permissions on.  
                   This value may not be populated for all roles.  Some built in roles have implicit permission
                   definitions.
ColumnName       : Name of the column of the object that the user/role is assigned permissions on. This value
                   is only populated if the object is a table, view or a table value function.                 
*/

DECLARE @HideDatabaseDiagrams BIT = 1;

--List all access provisioned to a sql user or windows user/group directly 
SELECT  
    [UserName] = CASE princ.[type] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                    WHEN 'R' THEN NULL -- Database Role
                    WHEN 'G' THEN NULL -- Windows Group
                    ELSE NULL
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'R' THEN NULL -- Database Role
                    WHEN 'G' THEN NULL -- Windows Group
                    ELSE princ.[type]
                 END,
    [PrincipalUserName] = COALESCE(
                    CASE princ.[type] 
                        WHEN 'S' THEN princ.[name]
                        WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                        WHEN 'R' THEN NULL -- Database Role
                        WHEN 'G' THEN NULL -- Windows Group
                        ELSE NULL
                     END,
                     princ.[name]
                 ),
    [PrincipalType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'R' THEN 'Database Role'
                    WHEN 'G' THEN 'Windows Group'
                 END, 
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[type] in ('S','U')
    AND CASE
        WHEN @HideDatabaseDiagrams = 1 AND
        princ.[name] = 'guest'
        AND (
            (
                obj.type_desc = 'SQL_SCALAR_FUNCTION'
                AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
            )
            OR (
                obj.type_desc = 'SQL_STORED_PROCEDURE'
                AND OBJECT_NAME(perm.major_id) IN
                (
                    N'sp_alterdiagram',
                    N'sp_creatediagram',
                    N'sp_dropdiagram',
                    N'sp_helpdiagramdefinition',
                    N'sp_helpdiagrams',
                    N'sp_renamediagram'
                )
            )
        )
        THEN 0
        ELSE 1
    END = 1
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[type] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                    WHEN 'R' THEN NULL -- Database Role
                    WHEN 'G' THEN NULL -- Windows Group
                    ELSE NULL
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'R' THEN NULL -- Database Role
                    WHEN 'G' THEN NULL -- Windows Group
                 END, 
    [PrincipalUserName] = COALESCE(
                    CASE memberprinc.[type] 
                        WHEN 'S' THEN memberprinc.[name]
                        WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                        WHEN 'R' THEN NULL -- Database Role
                        WHEN 'G' THEN NULL -- Windows Group
                        ELSE NULL
                     END,
                     memberprinc.[name]
                 ),
    [PrincipalType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'R' THEN 'Database Role'
                    WHEN 'G' THEN 'Windows Group'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE    
    CASE
        WHEN @HideDatabaseDiagrams = 1 AND
        memberprinc.[name] = 'guest'
        AND (
            (
                obj.type_desc = 'SQL_SCALAR_FUNCTION'
                AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
            )
            OR (
                obj.type_desc = 'SQL_STORED_PROCEDURE'
                AND OBJECT_NAME(perm.major_id) IN
                (
                    N'sp_alterdiagram',
                    N'sp_creatediagram',
                    N'sp_dropdiagram',
                    N'sp_helpdiagramdefinition',
                    N'sp_helpdiagrams',
                    N'sp_renamediagram'
                )
            )
        )
        THEN 0
        ELSE 1
    END = 1
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [PrincipalUserName] = '{All Users}',
    [PrincipalType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',       
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]                   
JOIN 
    --All objects   
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
    AND CASE
        WHEN @HideDatabaseDiagrams = 1 AND
        roleprinc.[name] = 'public'
        AND (
            (
                obj.type_desc = 'SQL_SCALAR_FUNCTION'
                AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
            )
            OR (
                obj.type_desc = 'SQL_STORED_PROCEDURE'
                AND OBJECT_NAME(perm.major_id) IN
                (
                    N'sp_alterdiagram',
                    N'sp_creatediagram',
                    N'sp_dropdiagram',
                    N'sp_helpdiagramdefinition',
                    N'sp_helpdiagrams',
                    N'sp_renamediagram'
                )
            )
        )
        THEN 0
        ELSE 1
    END = 1
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]

Answer 6

Unfortunately I couldn't comment on the Sean Rose post due to insufficient reputation, however I had to amend the "public" role portion of the script as it didn't show SCHEMA-scoped permissions due to the (INNER) JOIN against sys.objects. After that was changed to a LEFT JOIN I further had to amend the WHERE-clause logic to omit system objects. My amended query for the public perms is below.

--3) List all access provisioned to the public role, which everyone gets by default
    SELECT
        @@servername ServerName
        , db_name() DatabaseName
        , [UserType]         = '{All Users}',
        [DatabaseUserName] = '{All Users}',
        [LoginName]        = '{All Users}',
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Roles
        sys.database_principals            AS roleprinc
        --Role permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        --All objects
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = 'public'
        AND isnull(obj.[is_ms_shipped], 0) = 0
        AND isnull(object_schema_name(perm.[major_id]), '') <> 'sys'

ORDER BY
    [UserType],
    [DatabaseUserName],
    [LoginName],
    [Role],
    [Schema],
    [ObjectName],
    [ColumnName],
    [PermissionType],
    [PermissionState],
    [ObjectType]