SELECT
CASE dp.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'
ELSE dp.state_desc
END
+ ' ' + dp.permission_name + ' ON ' +
CASE dp.class
WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'
WHEN 1 THEN 'OBJECT::[' + SCHEMA_NAME(o.schema_id) + '].[' + o.[name] + ']'
WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(dp.major_id) + ']'
END
+ ' TO [' + USER_NAME(grantee_principal_id) + ']' +
CASE dp.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'
ELSE ';'
END
COLLATE DATABASE_DEFAULT
FROM sys.database_permissions dp
LEFT JOIN sys.all_objects o
ON dp.major_id = o.OBJECT_ID
WHERE dp.class < 4
AND major_id >= 0
AND grantee_principal_id <> 1;
http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/08/28/quick-2005-2008-script-to-export-permissions.aspx