Dump of SQL permissions?

SQL2K sp4
Is there a way to get a list of all assigned permissions in a DB? I'd like
to see it in a format like this:
Database X
Object Name Roles Users
Object X role X user1
user2
user 3
role Y user 1
user 3
user 25
etc
I came up with this, but it will not differentiate between Grants and
Denies, nor will it be formatted the way I need:
select so.Name ObjectName, su.Name UserName
from sysObjects so
inner join sysPermissions sp on so.id = sp.id
inner join sysUsers su on sp.grantee = su.uid
where so.name not like 'dt_%' and so.name not like 'sys%'
order by 1,2
TIA, ChrisRThis should give you the info you need, but you may need to rearrange the
output format.
(NOTE: The WHERE clause at the end just prevents listing the built-in
database roles, such as, db_owner)
select Users.UserName, Users.UserType, Users.SQLRoleName,
ObjPerm.Permission, ObjPerm.ObjName
from
(
select U.name UserName,
case when U.isntname = 1 and U.isntgroup <> 1 then 'NTuser'
when U.isntgroup = 1 then 'NTgroup'
when U.issqluser = 1 then 'SQLuser'
when U.issqlrole = 1 then 'SQLrole' end UserType, '' [SQLRoleName]
from dbo.sysusers U
union
select U.name, 'MemberOf' UserType, G.name [SQLRoleName]
from dbo.sysmembers M inner join dbo.sysusers U
on M.memberuid = U.uid
inner join dbo.sysusers G
on M.groupuid = G.uid
) Users
full join
(
select O.name ObjName, U.name UserName, (TY.name + ' ' + AC.name) Permission
from dbo.sysprotects PR inner join master.dbo.spt_values AC on PR.action =
AC.number
inner join master.dbo.spt_values TY on PR.protecttype = TY.number
inner join dbo.sysobjects O on PR.id = O.id
inner join dbo.sysusers U on PR.uid = U.uid
where AC.type = 'T' and TY.type = 'T'
) ObjPerm
on (Users.UserName = ObjPerm.UserName or Users.SQLRoleName = ObjPerm.UserNam
e)
where Users.UserName not like('db_%')
--
DJanson
"ChrisR" wrote:

> SQL2K sp4
> Is there a way to get a list of all assigned permissions in a DB? I'd like
> to see it in a format like this:
> Database X
> Object Name Roles Users
> Object X role X user1
> user2
> user 3
> role Y user 1
> user 3
> user 25
> etc
>
> I came up with this, but it will not differentiate between Grants and
> Denies, nor will it be formatted the way I need:
> select so.Name ObjectName, su.Name UserName
> from sysObjects so
> inner join sysPermissions sp on so.id = sp.id
> inner join sysUsers su on sp.grantee = su.uid
> where so.name not like 'dt_%' and so.name not like 'sys%'
> order by 1,2
> TIA, ChrisR
>