There is no simple SQL command to do this since the information you desire is stored within each project's schema.  So you would have to run the query for each project.  Maybe a better way is to use the SiteAdmin API or OTA API to get the users and roles for a project, then iterate through all projects in the domain.

This thread has some example code to do that.  You can also look in the API reference docs for code.

Hello All,

Finally, I come with the solution

we have to use Curesor to get User Projects with their associated roles.

USE [TEMP_DB]
GO
/****** Object:  StoredProcedure [dbo].[GetUserRoles]    Script Date: 07/24/2014 11:02:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUserRoles]
( @UserName varchar(20)
)
as
BEGIN 
DECLARE @name VARCHAR(50) -- database name  

Declare @GroupName varchar(50)
declare @sql varchar(max)
declare @deletetable varchar(50)
declare @projectName varchar(50)

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name In (SELECT PROJECTS.DB_NAME FROM [ALMsiteadmin_db].[td].USERS_PROJECTS,[ALMsiteadmin_db].[td].[USERS],[ALMsiteadmin_db].[td].PROJECTS
 where [USERS].USER_ID=USERS_PROJECTS.USER_ID
  and USERS_PROJECTS.[PROJECT_ID]=PROJECTS.PROJECT_ID 
  and USER_NAME=@UserName and PROJECTS.PR_IS_ACTIVE='Y')  
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
      
	set @projectName= (select PROJECTS.PROJECT_NAME as ProjectName from [ALMsiteadmin_db].td.Projects where PROJECTS.DB_NAME=@name)
     
     set @sql =('INSERT INTO [TEMP_DB].[dbo].[Temp] SELECT ''' @projectName ''' as ProjectName,  US_USERNAME as UserISID,(select GR_GROUP_NAME   '';'' from ['   @name   '].td.GROUPS WHERE SUBSTRING(US_GROUP, GR_GROUP_ID   1, 1) = 1 FOR XML PATH('''') ) Roles FROM ['   @name   '].td.USERS where USERS.US_USERNAME= ''' @UserName '''')
   
Exec(@sql)
       
FETCH NEXT FROM db_cursor INTO @name   

END  
select * from [TEMP_DB].[dbo].[Temp]
CLOSE db_cursor   
DEALLOCATE db_cursor 
END

 after creation of this Store procedure just execute the the SP

exec [dbo].[GetUserRoles] 'magdum' --UserName

you will get table which having ProjectName, UserName and their associated Roles.

Regards,

Ramesh

, How do I save this Crusor and execute in ORACLE, Please?

 NEW_ALM12  hope you are doing well. Are you able to build the stored procedure in Oracle, can you please  share it if available