You are viewing limited content. For full access, please sign in.

Question

Question

SQL Query to Find Forms Processes User Has Access Rights To

asked on October 16, 2024

Does anyone have a SQL query to query the Forms database to get a report of all the Forms processes that a user has been assigned access rights to. If there is a way to also determine the role, that would be great too.

0 0

Replies

replied on October 17, 2024 Show version history

Hi Blake,

Here is one I made for just getting the users and roles for each forms process...it does bring in team member names if it is a group given access, they would have the 'inherit' role_name, but the individual users given access would come up as submitter, admin, etc.  Hope this helps, it will at least give you a start with the table names and you could build it out further for additional data.  If you want to search for just the forms one particular user has access to, you could uncomment out the next to last line and put in the user's displayname between the empty quotes (last name followed by comma, then first name).

 

SELECT bp_id
       ,name
	   ,uir.user_id
	   ,user_role_id
	   ,displayname
	   ,rls.role_name
FROM [FORMS].[dbo].[cf_business_processes] AS bp
LEFT JOIN [FORMS].[dbo].[cf_users_in_role] AS uir
ON bp.bp_id = uir.bizprocess_applied 
LEFT JOIN [FORMS].[dbo].[cf_users] AS us
ON uir.user_id = us.user_id
LEFT JOIN [FORMS].[dbo].[cf_roles] AS rls
ON rls.role_id = uir.role_id
WHERE bp.is_activated = 1 AND uir.user_id IS NOT NULL
---AND displayname = ''
order by name

 

2 0
You are not allowed to follow up in this post.

Sign in to reply to this post.