I find it useful sometimes, to be able to obtain a list of specific permissions that were Granted or Denied to users (roles), in a SQL database.   For example, useful when producing evidence for a PCI DSS or SSAE16 security audit, for separation of duties, or just keeping the permissions matrix documentation up to date.

Here is the code I used for that:

DECLARE @outsql
VARCHAR(1000), @resultSQL NVARCHAR(MAX)
SELECT @resultSQL = ''
SELECT @outsql  = N'
SELECT
''##UserLogin##'' AS UserLogin, p.permission_name, p.state_desc, o.name, o.type_desc
FROM sys.database_permissions p INNER JOIN sys.objects o
ON p.major_id = o.object_id
WHERE grantee_principal_id = USER_ID(''##UserLogin##'')
UNION ALL
'

SELECT  @resultSQL = @resultSQL + REPLACE(@outsql , '##UserLogin##', [name])
FROM sys.sysusers
WHERE islogin = 1 AND (isntgroup = 1 OR isntuser = 1 OR issqluser = 1)

SELECT @resultSQL = @resultSQL + N'

       SELECT ''##UserLogin##'' AS UserLogin,
p.permission_name, p.state_desc, o.name, o.type_desc
FROM sys.database_permissions p INNER JOIN sys.objects o ON p.major_id = o.object_id
WHERE grantee_principal_id = -100

'

EXEC  sp_executesql @resultSQL

 

This produces results like this:

 

With a little tweaking, this code can be changed to instead re-create the actual GRANT and DENY SQL scripts (for PRINTing, not sp_executeSQL) to use somewhere else.