reporting on permissions 2

[denacho]

i'm very new to SQL (2000 SP3a) and my boss has requested a report that would give him the permissions assiged to users of a particular database. i have been able to find out that we assigned permissions by Roles. through the SQL Books Online, i was also able to understand the sp_helprotect and the sp_helprolemember. if i run the sp_helprotect, it will show the assignments to each role. i could then run the sp_helprolemember and see who is part of each role. is there a more succint way to get my info? i'd appreciate any help.

 

[mrdenny]

This code will give you the information that you are looking for.

select sysobjects.name,
	sysusers.name,
	case when action = 193 then
		'SELECT'
	when action = 195 then
		'INSERT'
	when action = 196 then
		'DELETE'
	when action = 197 then
		'UPDATE'
	when action = 224 then
		'EXECUTE'
	else
		'SOME OTHER RIGHT'
	end 'ACTION',
	case when protecttype = 204 then
		'GRANT_W_GRANT'
	when protecttype = 205 then
		'GRANT'
	when protecttype = 206 then
		'DENY'
	end 'AccessType'
from sysprotects
join sysobjects on sysprotects.id = sysobjects.id
join sysusers on sysprotects.uid = sysusers.uid

If you need more of the actions listed there is a table in books online under the heading "sysprotects system table" that will give you all the possible actions.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Denny,

You da bomb! Have a star, cause the code is certainly useful for me. @=)



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com