Licensing in SAP B1

[Former Member]

Hi All

Is there a report in SAP that provides a listing of users and what licence type has been assigned to that user?

Derek

[former_member184146]

Hi,

there is no such report but you can this under Admin >>Licenses

or you can try this query on SQL

if OBJECT_ID('tempdb..#xmlLicense') is not null

DROP TABLE #xmlLicense

;

if OBJECT_ID('tempdb..#UserLicenses') is not null

DROP TABLE #UserLicenses

;

CREATE TABLE #xmlLicense

(

        Id INT IDENTITY PRIMARY KEY,

        XMLData XML,

        LoadedDateTime DATETIME

)

CREATE TABLE #UserLicenses

(

        UserID varchar(50)

        , LicenseType varchar(100)

)

INSERT INTO #xmlLicense(XMLData, LoadedDateTime)

SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()

FROM OPENROWSET(BULK 'C:\Program Files (x86)\SAP\SAP Business One ServerTools\License\B1Upf.xml', SINGLE_BLOB) AS x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM #xmlLicense

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

INSERT INTO #UserLicenses

SELECT  UserID

                , LicenseType

FROM    OPENXML(@hdoc, 'Users/User/Modules/Module')

WITH

(

UserID [varchar](50) '../../UserName'

, LicenseType [varchar](100) 'KeyDesc'

)

EXEC sp_xml_removedocument @hDoc

GO

SELECT  T0.UserID, T1.U_NAME, T0.LicenseType

FROM    #UserLicenses T0

LEFT OUTER JOIN OUSR T1 ON T0.UserID COLLATE DATABASE_DEFAULT = T1.USER_CODE

--Manish

[Former Member]

Thanks Manish for your input - didn't work though. SAP upgrade being installed next Monday so will wait this then. Thanks again.

[former_member184146]

No Worries Derek , whenever you get chance you can test and let me know the result after testing.

Good Luck !

--Manish

[Former Member]

Hi Derek,

If you are using Sap b1 9.1 then you can see assignment tab on license administration window and you can export it to excel.

Else you can check on license administration window. No such report available.

Regards,

Chintan

[Former Member]

Thank you Chintan for coming back to me. Upgrading next month so will wait till then.

Derek