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'