Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Calling a Stored Procedure in DB User Import

AshishDas
Regular Contributor II
Regular Contributor II

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

12 REPLIES 12

nimitdave
Saviynt Employee
Saviynt Employee

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.

rushikeshvartak
All-Star
All-Star

https://www.mssqltips.com/sqlservertutorial/162/sql-server-stored-procedure-with-parameters/

Create Stored Procedure with Parameters


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

AshishDas
Regular Contributor II
Regular Contributor II

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:

AshishDas_0-1684838664860.png

2023-05-23T16:08:57+05:30-ecm-worker-{"log":"2023-05-23 10:38:56,186 [quartzScheduler_Worker-8] ERROR services.JobManagementGuardRailService - Error executing validateConflictingJob\n","stream":"stdout","time":"2023-05-23T10:38:56.18647545Z"}
 
2023-05-23T16:08:57+05:30-ecm-worker-{"log":"2023-05-23 10:38:56,515 [quartzScheduler_Worker-8] ERROR services.ImportSAvDataUserService - Error in Users Import - Import failed as no Users were inserted or updated\n","stream":"stdout","time":"2023-05-23T10:38:56.515879296Z"}

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

AshishDas
Regular Contributor II
Regular Contributor II

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

yatishtiwari
New Contributor III
New Contributor III

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

Yes separate using ; 


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

yatishtiwari
New Contributor III
New Contributor III

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

nimitdave
Saviynt Employee
Saviynt Employee

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>

yatishtiwari
New Contributor III
New Contributor III

Hi @nimitdave,

I tried this  <![CDATA[ call [PRQ].[dbo].[TS_spGetUsersList] ]]> and now its showing -

Could not find stored procedure 'call'.

Thanks,

Yatish

 

 

yatishtiwari
New Contributor III
New Contributor III

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

 

nimitdave
Saviynt Employee
Saviynt Employee

That has to be returned from the stored proc.