Adding, Editing and Deleting SQL User Information

You use SQL stored procedures to add, edit and delete users stored in the database. Note that you must have Administrative privileges to run these stored procedures.

The following three tables, listed here with their fields, store all information for the SQLUM:

CustomUM.User

  • UserID
  • UserName
  • UserPassword
  • PasswordHash
  • UserDescription
  • UserEmail
  • ManagerID
  • DisplayName

CustomUM.Group

  • GroupID
  • GroupName
  • GroupDescription

CustomUM.UserGroup

  • GroupID
  • UserID

You may want to edit these tables directly, but K2 recommends that you use the stored procedures to add, edit and delete users from the tables.

You may notice that the User table includes information about the user's manager. This is useful in cases where you need to assign tasks to the originator's manager, such as in a Leave Approval app, for the manger to action. Both the manager and the user need to have accounts in SQL. The users ManagerID field is the UserID field of his or her manager.

You must manually add users to the SQLUM tables. For security purposes, K2 provides a stored procedure which encrypts the passwords.

  1. Open Microsoft SQL Server Management Studio.
  2. Connect to the server where the K2 database is installed.
  3. Expand the K2 database (K2 is the default).
  4. Browse to the following path: K2 > Programmability > Stored Procedure
  5. Find CustomUM.CreateUser, right click and select Execute Stored Procedure.
  6. Enter the user's details on the Execute Procedure dialog.

To add a user to a group, you need to first create the group in the database and then add the user to that group. Create the group manually by following these steps:

  1. Open Microsoft SQL Server Management Studio.
  2. Connect to the server where the K2 database is installed.
  3. Expand the K2 database (K2 is the default).
  4. Right click on CustomUM.Group and select Edit Top 200 Rows.
  5. Enter the group details into the table (GroupID is automatically generated when you save your changes).

  6. Next you add users to the group. Right click on CustomUM.UserGroup and select Edit Top 200 Rows.
  7. Enter the GroupID (from CustomUM.Group entry you created) and the UserID (from CustomUM.Users, the stored procedure you ran when creating the user creates the ID) into the table and save your changes to the database.

To add a SQL user to a role, add the user to the role through K2 Management site > Users node > Roles. You must have administrator privileges to do this.

To edit a user's UserName, UserDescription, UserEmail, ManagerID or DisplayName, edit the relevant row in the CustomUM.User table.

To change a user's password, execute the CustomUM.SetPassword stored procedure and enter the Username and the new Password for the user whose password you want to change. The stored procedure will encrypt the new password.

If you need to remove a SQL user, please contact support for instructions.

See the following topics for more information about SQLUM: