cancel
Showing results for 
Search instead for 
Did you mean: 

Help: Querry to see all user and their licenses

Former Member
0 Kudos

Dear all,

I need a querry to see all user in a specific data base and licenses assigned to them.

I have 9 data base to chek this information, so i believe doing this through SQL querry would be easier.

I do appreciate any help from your side.

Thank you in advance.

Kind Regards,

Mariana

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

License allocation information is not retained in the database. Check the B1upf.xml file located in SAP\SAP Business One ServerTools\License.

Former Member
0 Kudos

Thank you Thomas

malcolm_lamour
Explorer
0 Kudos

Hi Mariana

Use this:

please check the file location if you get an error.

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

Answers (0)