Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Query that provides user's role and account information

sharu
New Contributor
New Contributor

Hi Team,

Can you please help me out with the SQL query that results user's having all the roles and accounts that are provisioned.

Thanks,

Sharanya V

 

 

6 REPLIES 6

rushikeshvartak
All-Star
All-Star

SELECT r.role_name,u,username,a.name,a.status
FROM roles r,role_user_account rua,users u,accounts a
WHERE rua.rolekey= r.rolekey and u.userkey=rua.userkey and a.accountkey=rua.accountkey


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

sharu
New Contributor
New Contributor

Hi @rushikeshvartak ,

 

Thanks for the reply, In the same query can we also check for endpoints provisioned for the user along with roles and account information.

If yes , can you please update the same

Thanks,

Sharanya

join with arstasks table and endpoints table


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

SELECT r.role_name,u,username,a.name,a.status,a.arstaskkey
FROM roles r,role_user_account rua,users u,accounts a
WHERE rua.rolekey= r.rolekey and u.userkey=rua.userkey and a.accountkey=rua.accountkey


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

@rushikeshvartak Can you please share ,e the update query

Thanks,

Sharanya V

Sharanya V

 

@sharu  please check below query:

SELECT
R.ROLE_NAME,
U.USERNAME,
A.NAME,
A.STATUS,
A.ARSTASKKEY,
E.ENDPOINTNAME
FROM
ROLES R
JOIN
ROLE_USER_ACCOUNT RUA ON RUA.ROLEKEY = R.ROLEKEY
JOIN
USERS U ON U.USERKEY = RUA.USERKEY
JOIN
ACCOUNTS A ON A.ACCOUNTKEY = RUA.ACCOUNTKEY
JOIN
ARSTASKS AR ON AR.ACCOUNTKEY = A.ACCOUNTKEY
JOIN
ENDPOINTS E ON AR.ENDPOINT = E.ENDPOINTKEY


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.