2 removed redundant empty lines from the code
source | link

Figured it out. A bad script granted database-wide SELECT permissions to the user. Found it using this script:

WITH    perms_cte as
 
(
 
        select USER_NAME(p.grantee_principal_id) AS principal_name,
 
                dp.principal_id,
 
                dp.type_desc AS principal_type_desc,
 
                p.class_desc,
 
                OBJECT_NAME(p.major_id) AS object_name,
 
                p.permission_name,
 
                p.state_desc AS permission_state_desc 
 
        from    sys.database_permissions p
 
        inner   JOIN sys.database_principals dp
 
        on     p.grantee_principal_id = dp.principal_id
 
)
 
--users
 
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
 
FROM    perms_cte p
 
WHERE   principal_type_desc <> 'DATABASE_ROLE'
 
UNION
 
--role members
 
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
 
FROM    perms_cte p
 
right outer JOIN (
 
    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
 
    from    sys.database_role_members rm
 
    INNER   JOIN sys.database_principals dp
 
    ON     rm.member_principal_id = dp.principal_id
 
) rm
 
ON     rm.role_principal_id = p.principal_id
 
order by 1

https://web.archive.org/web/20141128165740/http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

Figured it out. A bad script granted database-wide SELECT permissions to the user. Found it using this script:

WITH    perms_cte as
 
(
 
        select USER_NAME(p.grantee_principal_id) AS principal_name,
 
                dp.principal_id,
 
                dp.type_desc AS principal_type_desc,
 
                p.class_desc,
 
                OBJECT_NAME(p.major_id) AS object_name,
 
                p.permission_name,
 
                p.state_desc AS permission_state_desc 
 
        from    sys.database_permissions p
 
        inner   JOIN sys.database_principals dp
 
        on     p.grantee_principal_id = dp.principal_id
 
)
 
--users
 
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
 
FROM    perms_cte p
 
WHERE   principal_type_desc <> 'DATABASE_ROLE'
 
UNION
 
--role members
 
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
 
FROM    perms_cte p
 
right outer JOIN (
 
    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
 
    from    sys.database_role_members rm
 
    INNER   JOIN sys.database_principals dp
 
    ON     rm.member_principal_id = dp.principal_id
 
) rm
 
ON     rm.role_principal_id = p.principal_id
 
order by 1

https://web.archive.org/web/20141128165740/http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

Figured it out. A bad script granted database-wide SELECT permissions to the user. Found it using this script:

WITH    perms_cte as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_NAME(p.major_id) AS object_name,
                p.permission_name,
                p.state_desc AS permission_state_desc 
        from    sys.database_permissions p
        inner   JOIN sys.database_principals dp
        on     p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM    perms_cte p
WHERE   principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1

https://web.archive.org/web/20141128165740/http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

1
source | link

Figured it out. A bad script granted database-wide SELECT permissions to the user. Found it using this script:

WITH    perms_cte as

(

        select USER_NAME(p.grantee_principal_id) AS principal_name,

                dp.principal_id,

                dp.type_desc AS principal_type_desc,

                p.class_desc,

                OBJECT_NAME(p.major_id) AS object_name,

                p.permission_name,

                p.state_desc AS permission_state_desc 

        from    sys.database_permissions p

        inner   JOIN sys.database_principals dp

        on     p.grantee_principal_id = dp.principal_id

)

--users

SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name

FROM    perms_cte p

WHERE   principal_type_desc <> 'DATABASE_ROLE'

UNION

--role members

SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name

FROM    perms_cte p

right outer JOIN (

    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*

    from    sys.database_role_members rm

    INNER   JOIN sys.database_principals dp

    ON     rm.member_principal_id = dp.principal_id

) rm

ON     rm.role_principal_id = p.principal_id

order by 1

https://web.archive.org/web/20141128165740/http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx