Checking database permissions in SQL Server 2000

Working (test it)

select  su.name AS principal_name,

       dp.type_desc AS principal_type_desc,

       ao.type_desc,

       ao.name AS object_name,

       p.permission_name,

       p.state_desc AS permission_state_desc

from    master.sys.database_permissions p,

       master.sys.database_principals dp,

       master.sys.all_objects ao,

       master.sys.sysusers su

where ao.OBJECT_ID = p.MAJOR_ID

 and p.grantee_principal_id = dp.principal_id

 and p.GRANTEE_PRINCIPAL_ID = su.UID

 --and dp.name = 'guest'

Working (test it)

select USER_NAME(p.grantee_principal_id) AS principal_name,

        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

Working (test it)

WITH    perms_cte as

(

        select USER_NAME(p.grantee_principal_id) ASprincipal_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 asprincipal_type_desc, member_principal_id,user_name(member_principal_id) asmember_principal_name,user_name(role_principal_id) asrole_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

 ------------------------------------------------------------------------------------------------------------

http://blog.mrlacey.co.uk/2007/06/checking-database-permissions-in-sql.html