Permission List SQL 2005

This script gives a detailed information about permission for users in a database. This output lists Object name, Schema name, user name and permission. This is compatible only to SQL 2005.

Script

SELECT b.name,c.name,
USER_NAME(a.grantee_principal_id) AS UserName,a.permission_name,
CASE a.state
WHEN 'W' THEN 'With Grant Option'
WHEN 'G' THEN 'Grant'
WHEN 'D' THEN 'Deny'
ELSE ''
END AS PermissionType
FROM sys.database_permissions a,sys.all_objects b,sys.schemas c
WHERE b.object_id=a.major_id
AND a.grantee_principal_id<>0
AND a.grantee_principal_id<>2
AND b.schema_id=c.schema_id