Este script nos da el reporte de permisos por usuario y el de roles. Es muy últil cuando nos solicitan este tipo de información en una auditoria.
/************************************************************************************/
Autor: Marco A
DBASupport
/************************************************************************************/
select convert(varchar(10),u.name) as 'User',convert(varchar(20),o.name) as 'Table',
CASE action
WHEN 193 THEN 'Select'
WHEN 195 THEN 'Insert'
WHEN 196 THEN 'Delete'
WHEN 197 THEN 'Update'
END as Permission
from sysprotects p, sysusers u,sysobjects o
where p.uid = u.uid
and p.id = o.id
and p.action in (193,195,196,197)
order by 'User'
select uu.name as "Role",u.name as "User" from sysmembers m, sysusers u, sysusers uu
where u.uid = m.groupuid
and uu.uid = m.memberuid
and u.issqlrole = 1