Is there any way of finding all of the database objects and their permission
settings that are assigned to
a role in SQL 2005?

This was available in SQL 2000's interface :-(]


THANK YOU VERY MUCH!!! :-[)

Thanks for following up your own post. It prevents others from wasting =
their time trying to help you after you have solved the problem, and it =
helps others when you share your solution.

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc


Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous


[quoted text, click to view]
Here is a solution that I found (and slightly modified for this =
purpose) on the net from Kalen Delaney's article Managing Permissions =
that will return all of the db objects a user/role has permissions to:

CREATE PROC VP_Permissions_Granted
(@UserName sysname =3D NULL)

AS

BEGIN

DECLARE @uid INT

IF (SELECT uid FROM sysusers WHERE name =3D @UserName) IS NULL

BEGIN

PRINT 'You must supply a valid user name in the database'

RETURN

END

SELECT @uid =3D uid FROM sysusers WHERE name =3D @UserName

SELECT object =3D o.name, owner =3D user_name(o.uid),action =3D v.name

FROM sysprotects p join master..spt_values v

ON action =3D v.number AND v.type =3D 'T'

JOIN sysobjects o

ON o.id =3D p.id

WHERE p.uid =3D @uid

AND p.id > 100

AND protecttype IN (204,205)

UNION ALL

SELECT ' ', ' ', action =3D v.name

FROM sysprotects p join master..spt_values v

ON action =3D v.number AND v.type =3D 'T'

WHERE p.uid =3D @uid

AND p.id =3D 0

AND protecttype IN (204,205)

END




[quoted text, click to view]