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.