Courion Tip – Add a “Last Password Changed” Date/Time stamp to Identity Map

  • You can add a Last Password Change column to the identity map and populate only for successful password changes. Yes, the same information could be gotten by querying the ticketing (or, in my case, since we split it off – password_ticketing) table, but this is more convenient.  WARNING, if you do a COPY AND PASTE, the quotation marks will be ‘the wrong type’ please replace with the single quote that is straight up and down…

It’s just a matter of

  1. adding the column to the identitymap table in the database
  2. adding the stored procedure to populate it
  3. adding the trigger to the workflows in courion
  • In Microsoft SQL Server Management Studio Express, connect to your database, go under Tables, and Right Mouse click on IdentityMap. Click on Modify, and in the bottom of the list of columns, add LastPwdChange and set it as SmallDateTime. Leave all other values at the default. When you close this tab, it will prompt if you want to save it or not. Obviously, you do.
  • Here is the code for the Stored Procedure (for any new courion admins, Using Microsoft SQL Server Management Studio Express, connect to your database, go under Programmability, Stored Procedures, and right click on Stored Procedures. Click on “New Stored Procedure”. Paste the code below, but be sure to change the name of the database to match yours)

/****** Object: Procedure [dbo].[Update_IDMap_LastPWDChange] Script Date: 8/18/2010 1:15:12 PM ******/
USE [Name of your courion database goes here];
CREATE PROCEDURE [dbo].[Update_IDMap_LastPWDChange]
@ProfileUID varchar(25), @TargetID varchar(50)
UPDATE [IdentityMap]
SET LastPwdChange = getDate()
WHERE ProfileUID = @ProfileUID and TargetID = @TargetID


  • Now go into each workflo, Actions, Password Reset, Trigger Configuration. Click ADD, select the Microsoft ADO connector, the appropriate target (probably Courion), and the object should be ADO Trigger Object.
  • Name: Update_IDMap_LastPWDChange
  • Set Available to ON
  • Event to Resource Success
  • Leave the control type as Text Area
  • Check the Active box
  • and set the default value to this->

EXEC Update_IDMap_LastPWDChange ‘%Provisionee Community.User%’, ‘%TargetId%’

now TEST TEST TEST, and you should be all set.


Posted August 18, 2010 by mmdmurphy in Courion Tip

Tagged with

%d bloggers like this: