There are times when you need to audit user permissions to ensure that things aren’t getting inserted, over-written, or deleted. I looked around and found this script which uses several sys objects, which gives us most of the information we need.

select dp.NAME AS principal_name
     ,dp.type_desc AS principal_type_desc
     ,o.NAME AS object_name
     ,o.type_desc as Object_type
     ,p.permission_name
     ,p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
     on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
     on p.grantee_principal_id = dp.principal_id
order by o.NAME

Here is the link where I found the code to show all permissions in SQL server 2005, but the code works just fine in SQL Server 2008.