Very useful, though for anyone doing a more comprehensive security audit, I'd also run Vyaskin's code, which I modified only slightly, and which does not list sysadmin roles, but does list a lot of finer-grained information:
-- Original code: http://vyaskn.tripod.com/scripting_permissions_in_sql_server_2005.htm
-- Modified to be a more global report, additional QUOTENAME use, OBJECT:: syntax, and optimized hardcoded strings.
SELECT 'USE' + ' ' + QUOTENAME (DB_NAME()) AS '--Database Context'
SELECT 'EXEC sp_addrolemember @rolename = ' + QUOTENAME (USER_NAME (rm.role_principal_id),'''') + ', @membername = ' + QUOTENAME(USER_NAME (rm.member_principal_id)) AS '--Role Memberships'
FROM sys.database_role_members AS rm
ORDER BY USER_NAME (rm.role_principal_id) ASC, USER_NAME (rm.member_principal_id) ASC
-- works on table and column level permissions too
-- EXERCISE FOR THE READER: Column level permissions would be most efficiently granted with a comma separated list of columns for a given table.
-- The current code creates one statement per column, even on the same table!
SELECT
CASE
WHEN perm.state <> 'W'
THEN perm.state_desc
ELSE 'GRANT'
END + ' ' + perm.permission_name + ' ON OBJECT::' + QUOTENAME (USER_NAME (obj.schema_id)) + '.' + QUOTENAME (obj.name) +
CASE
WHEN cl.column_id IS NULL
THEN ''
ELSE '(' + QUOTENAME (cl.name) + ')'
END + ' TO ' + QUOTENAME(usr.name) COLLATE database_default +
CASE
WHEN perm.state <> 'W'
THEN ''
ELSE ' WITH GRANT OPTION'
END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl
ON cl.column_id = perm.minor_id
AND cl.[object_id] = perm.major_id
WHERE perm.class <> 3 -- NOT schema class
ORDER BY perm.permission_name ASC
,perm.state_desc ASC, usr.name ASC, USER_NAME (obj.schema_id) ASC, obj.name ASC
-- Schema level permissions
SELECT
CASE
WHEN perm.state <> 'W'
THEN perm.state_desc
ELSE 'GRANT'
END + ' ' + perm.permission_name + ' ON SCHEMA::' + QUOTENAME (sch.name)
+ ' TO ' + QUOTENAME(usr.name) COLLATE database_default +
CASE
WHEN perm.state <> 'W'
THEN ''
ELSE ' WITH GRANT OPTION'
END AS '--Schema Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.schemas AS sch
ON perm.major_id = sch.[schema_id]
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.class = 3 -- schema class
ORDER BY perm.permission_name ASC
,perm.state_desc ASC, usr.name ASC, sch.name ASC
-- Database level permissions.
SELECT
CASE
WHEN perm.state <> 'W'
THEN perm.state_desc
ELSE 'GRANT'
END + ' ' + perm.permission_name + ' TO ' + QUOTENAME(usr.name) COLLATE database_default +
CASE
WHEN perm.state <> 'W'
THEN ''
ELSE ' WITH GRANT OPTION'
END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
ORDER BY perm.permission_name ASC
,perm.state_desc ASC, usr.name ASC
SELECT 'USE [master];' AS '--Server Level Database Context'
SELECT
CASE
WHEN perm.state <> 'W'
THEN perm.state_desc
ELSE 'GRANT'
END + ' ' + perm.permission_name + ' TO ' + QUOTENAME(usr.name) COLLATE database_default +
CASE
WHEN perm.state <> 'W'
THEN ''
ELSE ' WITH GRANT OPTION'
END AS '--Server Level Permissions'
FROM sys.server_permissions AS perm
INNER JOIN sys.server_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
ORDER BY perm.permission_name ASC
,perm.state_desc ASC, usr.name ASC