Get All Permissions, Groups, roles for a user

[vamsivanka]

How to get all Permissions, groups, roles information for a user ?

[Kiran P]

Hi,

Look at the below post.

http://www.cognoise.com/index.php/topic,31182.0.html

Thanks,
Kiran

[Ravisha]

Assuming that the content store DB is SQL Server...

Below are the queries to retrieve User Groups & User Roles information.

/* User Groups */

SELECT DISTINCT
P.CMID AS UserID
,P.NAME AS FirstLastName
,O.NAME

FROM [dbo].[CMOBJPROPS33] (nolock) P
JOIN [dbo].[CMREFORD1] (nolock) N ON P.CMID = N.REFCMID
JOIN (SELECT CMID, NAME FROM [dbo].[CMOBJNAMES] (nolock) WHERE MAPDLOCALEID in (24)) O ON N.CMID = O.CMID
JOIN [dbo].[CMOBJECTS] J (nolock) ON O.CMID = J.CMID
JOIN [dbo].[CMCLASSES] C (nolock) ON J.CLASSID = C.CLASSID
JOIN [dbo].[CMOBJPROPS1] P1 (nolock) ON P.CMID = P1.CMID

WHERE (J.CLASSID in (54) OR J.CLASSID is null)
AND P.NAME is not null

/* User Roles */

SELECT DISTINCT
P.CMID AS UserID
,P.NAME AS FirstLastName
,O.NAME

FROM [dbo].[CMOBJPROPS33] (nolock) P
JOIN [dbo].[CMREFORD1] (nolock) N ON P.CMID = N.REFCMID
JOIN (SELECT CMID, NAME FROM [dbo].[CMOBJNAMES] (nolock) WHERE MAPDLOCALEID in (24)) O ON N.CMID = O.CMID
JOIN [dbo].[CMOBJECTS] J (nolock) ON O.CMID = J.CMID
JOIN [dbo].[CMCLASSES] C (nolock) ON J.CLASSID = C.CLASSID
JOIN [dbo].[CMOBJPROPS1] P1 (nolock) ON P.CMID = P1.CMID

WHERE (J.CLASSID in (26) OR J.CLASSID is null)
AND P.NAME is not null