05/19/2023 03:45 AM - edited 05/19/2023 03:51 AM
Hi,
We have a DB Source of Truth wherein we are allowed to call only stored procedures. We have a stored procedure created by the name [dbo].[GetSaviyntUsers] which can pull all details of the user table, like the firstname, lastname, usertypes etc.
How do we put filters on the usertypes while pulling the users onto saviynt. There are around 7 types of user types but we only need to pull 3 types of user types. How do we write it in our user import?
Any help will really be appreciated
05/21/2023 10:38 PM
Yes you can use a stored proc in user import xml json.
For filtering the users your db stored proc should accept some parameters and do the filtering.
05/21/2023 10:41 PM
https://www.mssqltips.com/sqlservertutorial/162/sql-server-stored-procedure-with-parameters/
05/23/2023 03:44 AM
Hi,
We see the following error while User Import. We are using direct tables to import and not Stored Procedures.
Any idea about the error:
05/23/2023 06:36 AM
As per the error it looks like a conflicting job was run ( may be other user imports job)
Can you check and run it again.
Thanks
Darshan
05/23/2023 09:55 PM
Still the same error. No other job was running.
We can see in logs that it pulls the data but is unable to insert it in Saviynt
06/06/2023 02:18 PM
Hi @rushikeshvartak @nimitdave ,
Can we combine 2 queries or exec statements in DB import JSON?
Like -
exec as login='<domain>\<username>'
exec [<db name>].[dbo].[listusers]
Thanks,
Yatish
06/06/2023 02:50 PM
Yes separate using ;
06/07/2023 12:32 PM - edited 06/07/2023 01:27 PM
Hi @rushikeshvartak ,
Thank you Rushi. But it kept failing. I tried following options-
Option 1-
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname">
<![CDATA[
call as login='ABC\username';call [PRQ].[dbo].[TS_spGetUsersList]
]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="UserName" type="character" />
</mapper>
</dataMapping>
Error message I am receiving is - "Incorrect syntax near the keyword 'as'."
Option 2-
Even when I am trying to run single command-
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname">
<![CDATA[
call [PRQ].[dbo].[TS_spGetUsersList]
]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="UserName" type="character" />
</mapper>
</dataMapping>
I am receiving following error - "Incorrect syntax near '.'."
Option 3-
if I am passing following-
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname">
<![CDATA[
"call exec as login='ABC\username';"
"call [PRQ].[dbo].[TS_spGetUsersList]"
]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="UserName" type="character" />
</mapper>
</dataMapping>
I am receiving following error - "Could not find stored procedure 'call exec as login=''ABC\username';'
Option 4-
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname">
<![CDATA[
"exec as login='ABC\username';"
"call [PRQ].[dbo].[TS_spGetUsersList]"
]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="UserName" type="character" />
</mapper>
</dataMapping>
I am receiving following error -"Could not find stored procedure 'exec as login='ABC\username';'"
I am on version 23.4.
Not sure what I am missing, please provide me any sample format.
Thanks,
Yatish
06/07/2023 09:31 PM
Try as below: SecuritySystem and endpoint are required
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname">
<![CDATA[ call [PRQ].[dbo].[TS_spGetUsersList] ]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="systemname" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementname" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="users.username" sourceproperty="username" type="character"/>
</mapper>
</dataMapping>
06/08/2023 03:32 AM
Hi @nimitdave,
I tried this <![CDATA[ call [PRQ].[dbo].[TS_spGetUsersList] ]]> and now its showing -
Could not find stored procedure 'call'.
Thanks,
Yatish
06/08/2023 04:57 AM
Hi @nimitdave,
Its working and status is true.
I tried following-
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname">
<![CDATA[ {call GAP70.dbo.TS_spGetInvestranUsersList()} ]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="UserName" type="character" />
</mapper>
</dataMapping>
But as its stored procedure, so endpoint and security system is not coming. So how to map hardcoded values in your approach?
Thanks,
Yatish
06/08/2023 06:56 AM
That has to be returned from the stored proc.