A company in central Chile sent me its annual maintenance contract and asked me to look at it before signing the renewal. Twenty-two licensed users. When we cross-checked who had even created a single document in the last six months, the real number was fourteen. The other eight kept costing money every month, quietly, with no way for anyone in finance to find out. That is what this tutorial on SAP Business One Licenses: measuring each user's real usage with SQL, before the next invoice arrives.
The problem is not really that companies buy too many licenses. The problem is that they buy well and then nobody audits SAP Business One Licenses. For example, a salesperson joins and gets a Professional license because that was what was available; that salesperson leaves two years later and the user stays there, half-locked, with the license still tied to their name. Multiply that by five turnovers and you already have a fixed cost nobody decided to take on.
Why auditing SAP Business One Licenses gives you budget back
Zylo, in its enterprise software management analysis, puts at around 53% the licenses organizations never use or use so little that they do not justify the spend. That figure, however, comes from the SaaS world, where waste shows up fast because it is billed every month. In SAP Business One the effect is slower and therefore more dangerous: most of the installed base in Chile is still perpetual licensing, so the overspend does not appear as an inflated subscription but as an annual maintenance fee calculated on a licensed value that stopped making sense three years ago.
There is a second cost, less obvious and more expensive. In fact, a Professional user costs considerably more than a Limited one, and the Professional license is almost always assigned out of convenience: nobody wants the salesperson calling on Monday to say he cannot open a screen. Yet when you measure which modules each person actually touches, a very clear pattern appears. Most operational users live inside a single functional domain. That salesperson who only issues quotations, orders and invoices does not need full access to the chart of accounts.
There is also a reason that has nothing to do with money. Every active license is an open door. A user who no longer works with you but still holds valid credentials is a concrete audit risk, and in a certification process that finding surfaces on its own.
Prerequisites before touching SAP Business One Licenses
Before running anything, gather these five things:
- Access with a superuser user in SAP Business One (License Administration will not open any other way).
- Read permission on the company database, either in SQL Server Management Studio or in HANA Studio.
- The current license file and the number of contracted licenses by type. Your partner has it; ask for it in writing.
- The date of your next maintenance renewal. Without that date, this exercise is interesting but it is not a negotiation.
- Half an hour from HR to cross the actual payroll against the user list.
A warning worth reading twice: there is no reliable table in the company database that tells you which license each user has been assigned. That information lives on the license server instead, out of reach from SQL. It is an SAP design decision that has bothered me for years, because it forces you to do half the job staring at a screen. The assignment is reviewed and changed in Administration > License > License Administration, and that is that.
Step-by-step tutorial to audit SAP Business One Licenses
Step 1: Build the SAP Business One Licenses assignment inventory
Log in as superuser and open Administration > License > License Administration. On the per-user assignment tab you will see the full list with the license type column. Then export that grid to Excel by right-clicking the table. This spreadsheet is your «what we pay for» side.
Step 2: Build the user inventory
Next, go to Administration > Setup > General > Users. Here you see every user code ever created, including the ones nobody uses anymore. But keep in mind a detail that surprises a lot of people: SAP Business One does not allow deleting users. You can only mark them as locked. So your list will always be longer than your payroll.
Step 3: Review the access log
Open Tools > Access Log. That window reads the USR5 table and shows you logins, logouts, password changes and failed attempts per user. It also works as a first filter, though do not take it as absolute truth yet; I explain why further on.
Step 4: Measure document activity with SQL
Here comes the part that really decides whether your SAP Business One Licenses are well assigned. Logging into the system does not mean working in the system. A user can open SAP every morning to look at a report and not have created a single document in a year, and that profile probably needs something else, not a Professional license. Run the queries in the next section in the Query Generator (Tools > Queries > Query Builder) or directly in your engine.
Step 5: Classify each user
Next, with the results in hand, build four groups: active users with a cross-functional profile (Professional justified), active users concentrated in one area (Limited candidates), users with no document activity but recent logins (review case by case) and users with neither activity nor logins (release).
Step 6: Validate with the managers before moving anything
Then send the classification to each area manager with a concrete question and a deadline. Do not ask «is this user useful?», because the answer is always yes. Ask «what breaks if we lock this user on the 15th?». The difference between those two questions is enormous.
Step 7: Release SAP Business One Licenses and document it
Finally, in License Administration remove the assignment from the approved users and lock the user in their record. The order matters. Also, keep a record in a simple document with the date, the user, who authorized it and which license was freed; that piece of paper is what lets you reach the renewal with a number instead of a feeling.
SQL queries to measure the real usage of your licenses
The following four queries are the heart of the SAP Business One Licenses audit. Each one comes in both versions, because the engine changes the syntax even though the data model is identical. Run them in order; each one answers a different question.
Query 1: User inventory and last document activity
This query goes through the header tables of the main documents and retrieves, for each user, how many they created and when the last one was. The UserSign field stores the identifier of the user who created the record and links to OUSR.USERID.
Una decisión de forma que te va a ahorrar un rato: el bloque de tablas va como tabla derivada dentro del Left Join, no como WITH. Las expresiones de tabla común son más elegantes de leer, pero el Generador de consultas de SAP Business One las trata mal y quedas peleando con el editor en vez de con los datos. La tabla derivada corre igual en SQL Server, en HANA y dentro de la propia ventana de consultas.
SQL Server
-- Query 1: inventario de usuarios y actividad documental real
SELECT
U.USER_CODE AS [Usuario],
U.U_NAME AS [Nombre],
CASE U.SUPERUSER WHEN 'Y' THEN 'Si' ELSE 'No' END AS [Superusuario],
CASE U.Locked WHEN 'Y' THEN 'Bloqueado' ELSE 'Activo' END AS [Estado],
U.lastLogin AS [Ultimo login],
COUNT(A.UserID) AS [Documentos],
MAX(A.Fecha) AS [Ultima actividad],
ISNULL(DATEDIFF(DAY, MAX(A.Fecha), GETDATE()), 9999) AS [Dias inactivo]
FROM OUSR U
LEFT JOIN (
SELECT UserSign AS UserID, CreateDate AS Fecha FROM OQUT
UNION ALL SELECT UserSign, CreateDate FROM ORDR
UNION ALL SELECT UserSign, CreateDate FROM ODLN
UNION ALL SELECT UserSign, CreateDate FROM OINV
UNION ALL SELECT UserSign, CreateDate FROM OPOR
UNION ALL SELECT UserSign, CreateDate FROM OPDN
UNION ALL SELECT UserSign, CreateDate FROM OPCH
UNION ALL SELECT UserSign, CreateDate FROM OIGN
UNION ALL SELECT UserSign, CreateDate FROM OIGE
UNION ALL SELECT UserSign, CreateDate FROM OWTR
UNION ALL SELECT UserSign, CreateDate FROM ORCT
UNION ALL SELECT UserSign, CreateDate FROM OVPM
UNION ALL SELECT UserSign, CreateDate FROM OCRD
UNION ALL SELECT UserSign, CreateDate FROM OITM
UNION ALL SELECT UserSign, CreateDate FROM OJDT WHERE TransType = 30
) A ON A.UserID = U.USERID
GROUP BY U.USER_CODE, U.U_NAME, U.SUPERUSER, U.Locked, U.lastLogin
ORDER BY [Dias inactivo] DESC
SAP HANA
-- Query 1: inventario de usuarios y actividad documental real
SELECT
U."USER_CODE" AS "Usuario",
U."U_NAME" AS "Nombre",
CASE U."SUPERUSER" WHEN 'Y' THEN 'Si' ELSE 'No' END AS "Superusuario",
CASE U."Locked" WHEN 'Y' THEN 'Bloqueado' ELSE 'Activo' END AS "Estado",
U."lastLogin" AS "Ultimo login",
COUNT(A."UserID") AS "Documentos",
MAX(A."Fecha") AS "Ultima actividad",
IFNULL(DAYS_BETWEEN(MAX(A."Fecha"), CURRENT_DATE), 9999) AS "Dias inactivo"
FROM OUSR U
LEFT JOIN (
SELECT "UserSign" AS "UserID", "CreateDate" AS "Fecha" FROM OQUT
UNION ALL SELECT "UserSign", "CreateDate" FROM ORDR
UNION ALL SELECT "UserSign", "CreateDate" FROM ODLN
UNION ALL SELECT "UserSign", "CreateDate" FROM OINV
UNION ALL SELECT "UserSign", "CreateDate" FROM OPOR
UNION ALL SELECT "UserSign", "CreateDate" FROM OPDN
UNION ALL SELECT "UserSign", "CreateDate" FROM OPCH
UNION ALL SELECT "UserSign", "CreateDate" FROM OIGN
UNION ALL SELECT "UserSign", "CreateDate" FROM OIGE
UNION ALL SELECT "UserSign", "CreateDate" FROM OWTR
UNION ALL SELECT "UserSign", "CreateDate" FROM ORCT
UNION ALL SELECT "UserSign", "CreateDate" FROM OVPM
UNION ALL SELECT "UserSign", "CreateDate" FROM OCRD
UNION ALL SELECT "UserSign", "CreateDate" FROM OITM
UNION ALL SELECT "UserSign", "CreateDate" FROM OJDT WHERE "TransType" = 30
) A ON A."UserID" = U."USERID"
GROUP BY U."USER_CODE", U."U_NAME", U."SUPERUSER", U."Locked", U."lastLogin"
ORDER BY "Dias inactivo" DESC
Query 2: SAP Business One Licenses that are candidates for release
El query anterior te da la foto completa. En cambio, este te da la lista corta: usuarios no superuser que llevan más de seis meses sin crear un solo documento, o que nunca crearon ninguno. Va autocontenido a propósito: trae otra vez la tabla derivada del Query 1 para que corra tal cual, sin que tengas que armar nada aparte.
SQL Server
-- Query 2: candidatos concretos a liberar. Corre tal cual, sin armar nada aparte.
SELECT
U.USER_CODE AS [Usuario],
U.U_NAME AS [Nombre],
U.E_Mail AS [Correo],
MAX(A.Fecha) AS [Ultima actividad],
CASE WHEN MAX(A.Fecha) IS NULL
THEN 'Nunca genero documentos'
ELSE 'Sin actividad hace mas de 6 meses' END AS [Diagnostico]
FROM OUSR U
LEFT JOIN (
SELECT UserSign AS UserID, CreateDate AS Fecha FROM OQUT
UNION ALL SELECT UserSign, CreateDate FROM ORDR
UNION ALL SELECT UserSign, CreateDate FROM ODLN
UNION ALL SELECT UserSign, CreateDate FROM OINV
UNION ALL SELECT UserSign, CreateDate FROM OPOR
UNION ALL SELECT UserSign, CreateDate FROM OPDN
UNION ALL SELECT UserSign, CreateDate FROM OPCH
UNION ALL SELECT UserSign, CreateDate FROM OIGN
UNION ALL SELECT UserSign, CreateDate FROM OIGE
UNION ALL SELECT UserSign, CreateDate FROM OWTR
UNION ALL SELECT UserSign, CreateDate FROM ORCT
UNION ALL SELECT UserSign, CreateDate FROM OVPM
UNION ALL SELECT UserSign, CreateDate FROM OCRD
UNION ALL SELECT UserSign, CreateDate FROM OITM
UNION ALL SELECT UserSign, CreateDate FROM OJDT WHERE TransType = 30
) A ON A.UserID = U.USERID
WHERE ISNULL(U.SUPERUSER, 'N') <> 'Y'
GROUP BY U.USER_CODE, U.U_NAME, U.E_Mail
HAVING MAX(A.Fecha) IS NULL
OR MAX(A.Fecha) < DATEADD(MONTH, -6, GETDATE())
ORDER BY MAX(A.Fecha) ASC
SAP HANA
-- Query 2: candidatos concretos a liberar. Corre tal cual, sin armar nada aparte.
SELECT
U."USER_CODE" AS "Usuario",
U."U_NAME" AS "Nombre",
U."E_Mail" AS "Correo",
MAX(A."Fecha") AS "Ultima actividad",
CASE WHEN MAX(A."Fecha") IS NULL
THEN 'Nunca genero documentos'
ELSE 'Sin actividad hace mas de 6 meses' END AS "Diagnostico"
FROM OUSR U
LEFT JOIN (
SELECT "UserSign" AS "UserID", "CreateDate" AS "Fecha" FROM OQUT
UNION ALL SELECT "UserSign", "CreateDate" FROM ORDR
UNION ALL SELECT "UserSign", "CreateDate" FROM ODLN
UNION ALL SELECT "UserSign", "CreateDate" FROM OINV
UNION ALL SELECT "UserSign", "CreateDate" FROM OPOR
UNION ALL SELECT "UserSign", "CreateDate" FROM OPDN
UNION ALL SELECT "UserSign", "CreateDate" FROM OPCH
UNION ALL SELECT "UserSign", "CreateDate" FROM OIGN
UNION ALL SELECT "UserSign", "CreateDate" FROM OIGE
UNION ALL SELECT "UserSign", "CreateDate" FROM OWTR
UNION ALL SELECT "UserSign", "CreateDate" FROM ORCT
UNION ALL SELECT "UserSign", "CreateDate" FROM OVPM
UNION ALL SELECT "UserSign", "CreateDate" FROM OCRD
UNION ALL SELECT "UserSign", "CreateDate" FROM OITM
UNION ALL SELECT "UserSign", "CreateDate" FROM OJDT WHERE "TransType" = 30
) A ON A."UserID" = U."USERID"
WHERE IFNULL(U."SUPERUSER", 'N') <> 'Y'
GROUP BY U."USER_CODE", U."U_NAME", U."E_Mail"
HAVING MAX(A."Fecha") IS NULL
OR MAX(A."Fecha") < ADD_MONTHS(CURRENT_DATE, -6)
ORDER BY MAX(A."Fecha") ASC
Query 3: Usage profile by functional area
This is the SAP Business One Licenses query that has saved the most money in the projects where I have applied it, and not because it finds dead users but because it finds over-licensed ones. The query counts each person's documents split by functional domain and also calculates how many different areas they really work in. A user who moves within a single area is a natural candidate for a Limited license; one who touches three or more justifies their Professional without discussion.
Un detalle que decide si este query sirve o miente: el filtro TransType = 30 sobre OJDT. Sin él, cuentas también los asientos que SAP genera solo, y como cada factura, cada pago y cada entrega valorizada crea su asiento automático, todos los usuarios aparecerían trabajando en finanzas. El query diría que nadie es candidato a Limited. Con el filtro quedan únicamente los asientos manuales, que sí son trabajo contable de verdad.
SQL Server
-- Query 3: perfil de uso por area funcional
SELECT
U.USER_CODE AS [Usuario],
SUM(CASE WHEN X.Area = 'VENTAS' THEN 1 ELSE 0 END) AS [Ventas],
SUM(CASE WHEN X.Area = 'COMPRAS' THEN 1 ELSE 0 END) AS [Compras],
SUM(CASE WHEN X.Area = 'INVENTARIO' THEN 1 ELSE 0 END) AS [Inventario],
SUM(CASE WHEN X.Area = 'FINANZAS' THEN 1 ELSE 0 END) AS [Finanzas],
SUM(CASE WHEN X.Area = 'PRODUCCION' THEN 1 ELSE 0 END) AS [Produccion],
COUNT(DISTINCT X.Area) AS [Areas distintas]
FROM OUSR U
LEFT JOIN (
SELECT UserSign AS UserID, 'VENTAS' AS Area FROM ORDR
UNION ALL SELECT UserSign, 'VENTAS' FROM OINV
UNION ALL SELECT UserSign, 'VENTAS' FROM ODLN
UNION ALL SELECT UserSign, 'COMPRAS' FROM OPOR
UNION ALL SELECT UserSign, 'COMPRAS' FROM OPCH
UNION ALL SELECT UserSign, 'COMPRAS' FROM OPDN
UNION ALL SELECT UserSign, 'INVENTARIO' FROM OIGN
UNION ALL SELECT UserSign, 'INVENTARIO' FROM OIGE
UNION ALL SELECT UserSign, 'INVENTARIO' FROM OWTR
UNION ALL SELECT UserSign, 'FINANZAS' FROM ORCT
UNION ALL SELECT UserSign, 'FINANZAS' FROM OVPM
UNION ALL SELECT UserSign, 'PRODUCCION' FROM OWOR
UNION ALL SELECT UserSign, 'FINANZAS' FROM OJDT WHERE TransType = 30
) X ON X.UserID = U.USERID
GROUP BY U.USER_CODE
ORDER BY [Areas distintas], [Usuario]
SAP HANA
-- Query 3: perfil de uso por area funcional
SELECT
U."USER_CODE" AS "Usuario",
SUM(CASE WHEN X."Area" = 'VENTAS' THEN 1 ELSE 0 END) AS "Ventas",
SUM(CASE WHEN X."Area" = 'COMPRAS' THEN 1 ELSE 0 END) AS "Compras",
SUM(CASE WHEN X."Area" = 'INVENTARIO' THEN 1 ELSE 0 END) AS "Inventario",
SUM(CASE WHEN X."Area" = 'FINANZAS' THEN 1 ELSE 0 END) AS "Finanzas",
SUM(CASE WHEN X."Area" = 'PRODUCCION' THEN 1 ELSE 0 END) AS "Produccion",
COUNT(DISTINCT X."Area") AS "Areas distintas"
FROM OUSR U
LEFT JOIN (
SELECT "UserSign" AS "UserID", 'VENTAS' AS "Area" FROM ORDR
UNION ALL SELECT "UserSign", 'VENTAS' FROM OINV
UNION ALL SELECT "UserSign", 'VENTAS' FROM ODLN
UNION ALL SELECT "UserSign", 'COMPRAS' FROM OPOR
UNION ALL SELECT "UserSign", 'COMPRAS' FROM OPCH
UNION ALL SELECT "UserSign", 'COMPRAS' FROM OPDN
UNION ALL SELECT "UserSign", 'INVENTARIO' FROM OIGN
UNION ALL SELECT "UserSign", 'INVENTARIO' FROM OIGE
UNION ALL SELECT "UserSign", 'INVENTARIO' FROM OWTR
UNION ALL SELECT "UserSign", 'FINANZAS' FROM ORCT
UNION ALL SELECT "UserSign", 'FINANZAS' FROM OVPM
UNION ALL SELECT "UserSign", 'PRODUCCION' FROM OWOR
UNION ALL SELECT "UserSign", 'FINANZAS' FROM OJDT WHERE "TransType" = 30
) X ON X."UserID" = U."USERID"
GROUP BY U."USER_CODE"
ORDER BY "Areas distintas", "Usuario"
Query 4: Real logins from the USR5 log
The USR5 table stores the access log: every login, logout, password change and failed attempt is recorded with its date, its time and the machine's IP. Note that Date is a reserved word, so in SQL Server it goes in square brackets and in HANA in double quotes.
SQL Server
-- Query 4: accesos reales desde el registro USR5
SELECT
L.UserCode AS [Usuario],
COUNT(*) AS [Eventos de acceso],
MIN(L.[Date]) AS [Primer acceso en la ventana],
MAX(L.[Date]) AS [Ultimo acceso],
DATEDIFF(DAY, MAX(L.[Date]), GETDATE()) AS [Dias sin entrar]
FROM USR5 L
WHERE L.[Date] >= DATEADD(MONTH, -12, GETDATE())
GROUP BY L.UserCode
ORDER BY [Dias sin entrar] DESC
SAP HANA
-- Query 4: accesos reales desde el registro USR5
SELECT
L."UserCode" AS "Usuario",
COUNT(*) AS "Eventos de acceso",
MIN(L."Date") AS "Primer acceso en la ventana",
MAX(L."Date") AS "Ultimo acceso",
DAYS_BETWEEN(MAX(L."Date"), CURRENT_DATE) AS "Dias sin entrar"
FROM USR5 L
WHERE L."Date" >= ADD_MONTHS(CURRENT_DATE, -12)
GROUP BY L."UserCode"
ORDER BY "Dias sin entrar" DESC
Variations and use cases
The same material gets reused in four different scenarios, because a SAP Business One Licenses review is good for much more than cutting costs.
Automatic quarterly audit. Save Query 2 as a view and hook it to an internal alert so it lands in your inbox every three months. Regular follow-up keeps the work from turning into an annual crusade. If you want the detail on how to make these queries reusable, check the article on change log auditing.
Preparing the negotiation with your partner. When you discuss SAP Business One Licenses with your provider, bring Query 3 filtered by single-area users to the table. Instead of asking for a discount, propose a reconfiguration of the Professional/Limited mix backed by twelve months of data. That is a different conversation.
Onboarding and offboarding. Use Query 1 as a baseline and add the hire date from your HR system. That way you detect the reverse case to the one you were looking for: people who joined four months ago and still do not have access to what they need.
Companies with several legal entities. If you run multiple companies, run Query 1 on each database and consolidate. The same user code can be consuming a license in three databases while working in one.
Best practices and common mistakes
The mistakes that cost the most
Do not lock users without warning. This one has cost me dearly. I locked a user on a Friday afternoon, convinced it was a ghost because it had gone months without opening the interface, and on Monday the night shift's invoicing integration went down. That «user» was a service account. In general, integration accounts do not behave like human users: some post documents under the service user and others leave no visible trace in the header tables, so they can look inactive in every query above. Flag them separately before touching anything.
Locking does not release the license. This is the mistake I have seen most often in other people's audits. You flag the user as locked in their record, you walk away relaxed, and the license stays assigned to their name taking up a seat. They are two separate actions, on two different screens, and the system does not warn you. You have to go to License Administration and remove the assignment explicitly.
Two measurement traps in SAP Business One Licenses
Careful with counting USR5 rows as logins. The field Action mixes events: successful logins, failed attempts, password changes, superuser selection. For example, a user who forgot their password and retried eight times shows more «activity» than one who worked eight hours. Check the values of Action in your own database before drawing conclusions.
The lastLogin field is not always reliable. I believe it was already there in 9.3, though I would not bet my hand on the exact version. The point is another: depending on how the user connects (Windows client, Web Client, Service Layer), that field may not refresh. That is why this tutorial crosses three sources instead of trusting one.
Best practices when releasing SAP Business One Licenses
Uppercase in HANA. Esta se paga cara y no perdona. En SQL Server da lo mismo escribir Superuser o SUPERUSER, porque el motor no distingue; en HANA, como los identificadores van entre comillas dobles, la columna se llama exactamente "SUPERUSER" y cualquier otra forma revienta el query. OUSR mezcla los tres estilos: "USERID" and "USER_CODE" en mayúsculas, "Locked" and "E_Mail" capitalizadas, "lastLogin" en camello. Por lo tanto, si HANA te reclama por un nombre de columna, no toques la lógica: revisa la escritura exacta con un SELECT * sobre OUSR.
Save evidence before releasing. Finally, export the result of the four queries with that day's date and file it. If someone complains in March, you have the backup from October.
Official SAP resources
- License Guide for SAP Business One 10.0: license types and the functional scope of each one.
- Allocating Licenses (SAP Help Portal): the official assignment procedure.
- OUSR (Users) table in the SDK: user field definitions.
- USR5 (User Access Log) table in the SDK: structure of the access log.
Conclusion: SAP Business One Licenses under control
A license audit is not a project. It is four queries, two configuration screens and an honest conversation with the area managers, and the result in the companies where I have done the exercise is almost never zero: you find licenses tied to people who are no longer there, and you find Professional users who have spent two years working inside a single module. Hard data changes the conversation with your partner. Without it, the renewal is a formality; with it, it is a negotiation.
Run Query 1 today, even out of curiosity. You will find at least one user who surprises you, probably that «consultor2019» nobody can trace. And if reviewing your SAP Business One Licenses gives you budget back, tell me about it: I want to know how much.
This tutorial is part of the practical SAP Business One library of the Círculo Estratégico. If you want the rest of the tutorials with the queries ready to copy, check the subscriptions section.
