How to get user access report using SQL query

4 minute read time.

This script a quick way to check user’s permissions to Sage 500 tasks and security events. It is based on the explicit permissions to the tasks and security events stored in tables tsmSecurEventPerm and tsmTaskPerm. If the records do not exist in either table the task or security events will not be listed in the results set meaning that the user has no access to the task or security event in a particular company. The script is a start point to get the list of user permissions and you can edit and upgrade with more SQL code to include the list of all tasks where user is not explicitly excluded (in tables tsmSecurEventPerm and tsmTaskPerm) but effectively is. Remember that if the user is in more than one security group for the same company, the system grants the user access permissions of the security group with the highest access permissions level.

The script below will  a view named vdvUser_Access consisting of the underlining tables where the access to Sage 500 tasks and security events is defined.

 

Database Warning: These steps require knowledge of database engines and application databases (DBs) used by your Sage product (including Microsoft/Transact SQL, Pervasive SQL, or MySQL, etc.). Customer Support is not responsible for assisting with these steps and cannot be responsible for errors resulting from changes to the database engine or DBs. Before making changes, backup all system and application DBs required for a full restore. Contact an authorized business partner or DB administrator for assistance.

 

 

CREATE VIEW vdvUser_Access AS

SELECT tsmModuleDef.ModuleID, tsmModuleStrDef.ModuleDefName, tsmLocalString.LocalText Description, vsmLoginWrk.UserID, MAX (tsmSecurEventPerm.Authorized ) Rights ,

CASE MAX(tsmSecurEventPerm.Authorized) WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END Permissions,

tsmUserCompanyGrp.CompanyID, tsmUserCompanyGrp.UserGroupID

FROM tsmSecurEvent WITH (NOLOCK)

LEFT OUTER JOIN tsmSecurEventPerm WITH (NOLOCK) ON tsmSecurEvent.SecurEventID = tsmSecurEventPerm.SecurEventID

JOIN tsmLocalString WITH (NOLOCK) ON tsmSecurEvent.DescStrNo = tsmLocalString.StringNo AND tsmLocalString.LanguageID = 1033

JOIN tsmModuleDef WITH (NOLOCK) ON tsmSecurEvent.ModuleNo = tsmModuleDef.ModuleNo

JOIN tsmModuleStrDef ON tsmSecurEvent.ModuleNo=tsmModuleStrDef.ModuleNo

JOIN tsmUserCompanyGrp on tsmSecurEventPerm.UserGroupID=tsmUserCompanyGrp.UserGroupID

JOIN vsmLoginWrk ON tsmUserCompanyGrp.UserID=vsmLoginWrk.UserID

WHERE vsmLoginWrk.IsAcuityUser = 1

GROUP BY tsmModuleDef.ModuleID, tsmModuleStrDef.ModuleDefName, tsmLocalString.LocalText , vsmLoginWrk.UserID, tsmUserCompanyGrp.CompanyID, tsmSecurEventPerm.Authorized,tsmUserCompanyGrp.UserGroupID

UNION

SELECT tsmModuleDef.ModuleID, tsmModuleStrDef.ModuleDefName, tsmTaskStrDef.TaskDesc Description, tsmUser.UserID, MAX (tsmTaskPerm.Rights) Rights,

vListValidationString.LocalText Permissions,

tsmUserCompanyGrp.CompanyID, tsmUserCompanyGrp.UserGroupID

FROM ((tsmUser INNER JOIN tsmUserCompanyGrp ON tsmUser.UserID=tsmUserCompanyGrp.UserID)

INNER JOIN tsmTaskPerm ON tsmUserCompanyGrp.UserGroupID=tsmTaskPerm.UserGroupID)

INNER JOIN tsmTaskStrDef ON tsmTaskPerm.TaskID=tsmTaskStrDef.TaskID

INNER JOIN vsmLoginWrk ON tsmUser.UserID=vsmLoginWrk.UserID

INNER JOIN tsmTask ON tsmTaskStrDef.TaskID=tsmTask.TaskId

INNER JOIN tsmModuleDef ON tsmTask.ModuleNo=tsmModuleDef.ModuleNo

INNER JOIN tsmModuleStrDef ON tsmTask.ModuleNo=tsmModuleStrDef.ModuleNo

INNER JOIN vListValidationString ON tsmTaskPerm.Rights=vListValidationString.DBValue

WHERE vsmLoginWrk.IsAcuityUser = 1 AND vListValidationString.ColumnName='Rights' AND vListValidationString.TableName='tsmTaskPerm'

GROUP BY tsmModuleDef.ModuleID, tsmModuleStrDef.ModuleDefName, tsmTaskStrDef.TaskDesc, tsmUser.UserID, tsmUserCompanyGrp.CompanyID, tsmUserCompanyGrp.UserGroupID, vListValidationString.LocalText

After the view has been created you can run a SQL query to get user's permissions. So for example to get the permissions for user admin in company SOA your query will be like this:

SELECT * FROM vdvUser_Access  WHERE UserID='admin' and CompanyID='SOA'