SELECT
OBJECT_SCHEMA_NAME(major_id) as SchemaName,
OBJECT_NAME(major_id) as ObjectName,
USER_NAME(grantee_principal_id)
as LoginName,
permission_name,
'REVOKE ' + permission_name + ' ON ' + OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) + ' TO ' + USER_NAME(grantee_principal_id)
FROM
sys.database_permissions
p
left join sys.syslogins l on USER_NAME(grantee_principal_id)
= l.name
where
USER_NAME(grantee_principal_id) not in ('public', 'guest')
and
permission_name <> 'CONNECT'
and OBJECT_NAME(major_id) is not null
ORDER BY USER_NAME(grantee_principal_id)
