USP_APPUSERPASSWORDRESET_GETBYTOKENANDCLEANUP

CREATE procedure dbo.USP_APPUSERPASSWORDRESET_GETBYTOKENANDCLEANUP
(
  @TOKEN nvarchar(50), 
  @EXPIREHOURS int,
  @CHANGEAGENTID uniqueidentifier
)
as
begin

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @contextCache varbinary(128);

  /* cache current context information */
  set @contextCache = CONTEXT_INFO();

  /* set CONTEXT_INFO to @CHANGEAGENTID */
  if not @CHANGEAGENTID is null
    set CONTEXT_INFO @CHANGEAGENTID;

  -- Delete all expired reset requests.

  delete from dbo.APPUSERPASSWORDRESET where DATEADDED < dateadd(hour, -@EXPIREHOURS, getdate());

  /* reset CONTEXT_INFO to previous value */
  if not @contextCache is null
    set CONTEXT_INFO @contextCache;

  declare @WSFEDERATIONENABLED bit;
  set @WSFEDERATIONENABLED = case when exists(select top 1 1 from dbo.CONDITIONSETTING where NAME = 'WSFederation') then 1 else 0 end;

  -- Open the symmetric key for encryption

  exec dbo.USP_GET_KEY_ACCESS;

  select
    APPUSERPASSWORDRESET.ID,
    APPUSERPASSWORDRESET.APPUSERID,
    APPUSER.USERSID
  from dbo.APPUSERPASSWORDRESET
  inner join dbo.APPUSER on APPUSER.ID = APPUSERPASSWORDRESET.APPUSERID
  left outer join dbo.APPUSERCLAIMS on APPUSER.ID = APPUSERCLAIMS.APPUSERID
  where /*APPUSERPASSWORDRESET.TOKENINDEX = dbo.UFN_GET_MAC_FOR_TEXT(@TOKEN, 'dbo.APPUSERPASSWORDRESET')
    and*/ DecryptByKey(TOKEN) = @TOKEN collate SQL_Latin1_General_CP1_CS_AS -- case-sensitive

    and (@WSFEDERATIONENABLED = 0 or (@WSFEDERATIONENABLED = 1 and APPUSERCLAIMS.NAMEIDENTIFIER is null));

  close symmetric key sym_BBInfinity;

end