Snippet Name: Check_PeopleSoft_User_Lockout

Tags:

Created Date: 2025-06-16 13:12:39
Last Modified Date: 2025-05-31 10:32:07

Dependencies:

DBA Notes:

Sample Output:

Title: Check PeopleSoft User Lockout
Author: user
in the application "peopleSoft", the user "people is reported as locked out, but the error is not SQL server related, but related to the application. how can I check if user  "people"  is locked or not in each of my databases realted to the applciaiton?
 
 

Author: ChatGPT
In PeopleSoft, user authentication and account lockouts are typically managed within the **PeopleSoft application tables** rather than SQL Server logins. To check if the user **"people"** is locked out across all your PeopleSoft-related databases, you need to query the relevant application tables.

### Steps to Check for Lockout in PeopleSoft:

1. **Check the `PSOPRDEFN` Table**  
   The `PSOPRDEFN` table contains PeopleSoft user definitions, including lockout status.

   ```sql
   SELECT OPRID, ACCTLOCK, LOCK_REASON, LASTSIGNONDTTM
   FROM PSOPRDEFN
   WHERE OPRID = 'people';
   ```

   - `ACCTLOCK`:  
     - `0` ??? Account is **not** locked.  
     - `1` ??? Account **is** locked.  
   - `LOCK_REASON`: May provide additional details on why the account is locked.
   - `LASTSIGNONDTTM`: Last successful login attempt.

2. **Check for Invalid Login Attempts**  
   If a user is getting locked out frequently, you can review their sign-on audit records.

   ```sql
   SELECT OPRID, LOGINDTTM, LOGOUTDTTM, LOGINSTATUS
   FROM PSACCESSLOG
   WHERE OPRID = 'people'
   ORDER BY LOGINDTTM DESC;
   ```

   - `LOGINSTATUS`:  
     - `0` ??? Successful login  
     - `1` ??? Failed login attempt  

3. **Check Across All PeopleSoft Databases**  
   If you have multiple databases related to PeopleSoft, you can run the above queries on each database using **dynamic SQL**. Example:

   ```sql
   DECLARE @sql NVARCHAR(MAX) = '';

   SELECT @sql = @sql + 
   'SELECT ''' + name + ''' AS DatabaseName, OPRID, ACCTLOCK, LOCK_REASON 
    FROM [' + name + '].dbo.PSOPRDEFN WHERE OPRID = ''people'' UNION ALL '
   FROM sys.databases
   WHERE name LIKE 'HR%' OR name LIKE 'FSCM%' OR name LIKE 'CRM%'; -- Adjust as needed

   -- Remove last "UNION ALL"
   SET @sql = LEFT(@sql, LEN(@sql) - 10);

   EXEC sp_executesql @sql;
   ```

   This will check for the user status in all databases with names matching PeopleSoft modules like **HRMS, FSCM, CRM**.

### Next Steps:
- If `ACCTLOCK = 1`, you may need to **unlock the user** via the PeopleSoft application.
- If the issue persists despite `ACCTLOCK = 0`, check **web server logs, integration broker logs, and process scheduler logs** for further clues.

Let me know if you need help adjusting the query for your specific environment!