Developer ToolsFree generatorPDF, Excel, Word exportInfor SyteLineAll Infor SyteLine (CloudSuite Industrial) tools

SyteLine Users and Groups Query Generator for Security Reviews

This generator writes read-only SQL for reviewing who can do what in Infor SyteLine 9 and 10 on-prem: user to group membership, users that belong to no group, group sizes, and the authorization rows for a single form. It is meant for periodic access reviews and audit evidence, not for changing security.

Fill in

User name filter (LIKE)

Group name filter (LIKE)

Users table

User to group map table

Some databases expose this as UserGroupMap_mst; use block 0 to see which exists.

Groups table

Authorizations table

May be AccountAuthorizations_mst on your database. Check block 0.

Authorizations column holding the object name

Verify from block 0 output before running block 4.

Form name for block 4

Generated code

Download:

0. Find the security tables and their columns

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE N'UserNames%'
   OR TABLE_NAME LIKE N'UserGroupMap%'
   OR TABLE_NAME LIKE N'GroupNames%'
   OR TABLE_NAME LIKE N'AccountAuthorizations%'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

1. Users with their groups

SELECT
    u.UserId,
    u.Username,
    u.UserDesc,
    g.GroupId,
    g.GroupName
FROM dbo.UserNames u
JOIN dbo.UserGroupMap m
    ON m.UserId = u.UserId
JOIN dbo.GroupNames g
    ON g.GroupId = m.GroupId
WHERE u.Username LIKE N'%'
  AND g.GroupName LIKE N'%'
ORDER BY u.Username, g.GroupName;

2. Users in no group

SELECT u.UserId, u.Username, u.UserDesc
FROM dbo.UserNames u
WHERE u.Username LIKE N'%'
  AND NOT EXISTS (
      SELECT 1
      FROM dbo.UserGroupMap m
      WHERE m.UserId = u.UserId
  )
ORDER BY u.Username;

3. Member count per group

SELECT
    g.GroupId,
    g.GroupName,
    COUNT(DISTINCT m.UserId) AS members
FROM dbo.GroupNames g
LEFT JOIN dbo.UserGroupMap m
    ON m.GroupId = g.GroupId
WHERE g.GroupName LIKE N'%'
GROUP BY g.GroupId, g.GroupName
ORDER BY members DESC, g.GroupName;

4. Authorization rows for one form (verify structure first)

-- Confirm the table name and the object name column from block 0 before running
SELECT a.*
FROM dbo.AccountAuthorizations a
WHERE a.[ObjectName1] = N'CustomerOrders';

UserNames (UserId, Username, UserDesc), GroupNames and the user to group map are the Mongoose security tables behind the Users and Groups forms; whether a table carries the _mst suffix and which columns the authorization table has can differ between SyteLine 9 and 10, so run block 0 and correct the fields. Read-only: change users, groups and authorizations only through the Users and Groups forms and the object authorization forms opened from them, never with UPDATE or DELETE.

Preparing for an access review?

Send us your block 0 output and we will confirm the queries for your SyteLine version and suggest the evidence pack your auditor will expect.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Run block 0 first. It lists every table whose name starts with the security table names, with their columns, so you can see whether your database uses UserNames and UserGroupMap or _mst variants, and how the authorization table is laid out. Adjust the table and column fields to match. Then run block 1 with a group filter such as %Purchasing% to get the evidence list your auditor asks for, block 2 to find accounts with no group (often leftovers or service accounts), and block 3 to spot groups that have grown far beyond their purpose. Block 4 returns the raw authorization rows for one form name, which you then read together with the group IDs from block 1.

Export blocks 1 and 3 to Excel as quarterly access review evidence
Investigate every row in block 2
Use a read-only SQL login for all of it

Where security lives in SyteLine

SyteLine security is Mongoose security. Users are defined in the Users form and stored in UserNames, keyed by UserId with the login name in Username. Groups are defined in the Groups form and stored in GroupNames. Membership is a separate mapping table keyed by UserId and GroupId. Object level rights, such as which groups can read, insert, update or delete through a form or IDO, are maintained in the object authorization forms for groups and users and stored in the account authorizations table. Effective rights are the combination of a user's groups and any user-level authorizations, plus super user status, which bypasses group rights entirely. That is why a SQL list of groups is evidence, but the forms remain the authority for what a user can actually do.

Login and password questions

Passwords are never something to read or set in SQL. Users change their own password from the sign-in dialog or their user settings, and administrators reset it in the Users form, which applies the password rules configured for the system. If your SyteLine 10 installation uses Windows or federated sign-in, the password is owned by your directory, not by SyteLine. A typical login problem on 8, 9 or 10 is one of: the user is not linked to the right configuration, the account is disabled or locked after failed attempts, or the user has no group with access to the starting forms. Blocks 1 and 2 answer the third case quickly.

Worked example

For a quarterly review of purchasing access, set the group filter to %PUR%. Block 1 returns 28 rows for 19 users across three groups. Block 3 shows the PUR_APPROVE group has 11 members while the policy says four. Block 2 shows two accounts in no group, one a departed contractor and one an integration service account. The actions are recorded and done in the Users and Groups forms, and the queries are rerun to show the after state as evidence.

Frequently Asked Questions

How do I change my password in SyteLine?

Use the change password option in the sign-in dialog or your user preferences, depending on the client. An administrator can reset a password in the Users form. Never update password columns with SQL: the stored value is protected and the forms apply your password policy.

Why can a user log in to SyteLine 10 but not open forms?

Signing in only proves the account exists. Opening a form needs authorization through a group or a user-level grant. Run block 1 for that user to see their groups, then check the object authorizations for that form on each of those groups.

Which table links SyteLine users to groups?

The user to group map, commonly UserGroupMap (or a _mst variant), with UserId and GroupId columns that join to UserNames and GroupNames. Confirm the exact name on your database with block 0 before using it in a report.

Can I bulk-add users to groups with SQL?

Do not. Group membership changes made outside the forms skip the validation and change history the forms provide, and are hard to audit afterwards. Use the Users and Groups forms, or an approved IDO-based import, and keep these queries for checking the result.

If you want an assistant that answers SyteLine questions while respecting these same user groups, on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools