Configuring Reporting Console Database Access

Access to the LT Auditor ^MP Reporting Console is controlled through a combination of a SQL Server database role and an Active Directory security group. This approach centralizes access management and ensures only authorized users can run reports from the console.

Prerequisites:

Before beginning, confirm you have:

• SQL Server access with administrative privileges
• Active Directory access to create and manage security groups
• Access to the LT Auditor ^MP production database (LTAProductionDB)

Step 1 — Create an Active Directory security group:

1. Open Active Directory Users and Computers
2. Create a new security group (e.g., LT Auditor Report Users)
3. Add all users who require access to the Reporting Console to this group

[Your administrator should confirm the naming convention and OU location for new security groups in your environment.]

Step 2 — Create the SQL Server role and assign permissions:

Run the following SQL script against the LTAProductionDB database. This script creates the ReportUsers role and grants it the necessary execute permissions on stored procedures and select permissions on views required by the Reporting Console.

\-- ============================================================
-- Create Role
-- ============================================================
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'ReportUsers')
BEGIN
    CREATE ROLE ReportUsers;
END
GO

-- ============================================================
-- Grant EXECUTE on Stored Procedures
-- ============================================================
GRANT EXECUTE ON OBJECT::dbo.usysr_RptUserSAll TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usys_LkpConsS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usys_LkpConsSelectByAuditSS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usys_LkpConsSelectByAuSS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usys_LkpConsSelectByOperation TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usys_LkpConsSelectSelected TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usys_LKPOPsS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usys_LkpOpsSByAuSS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usys_LkpOpsSForCPAAuSS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usys_LkpOpsSSed TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysc_AuditSubsystemsS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysc_AuditSubsystemsSAll TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysc_AuditSubsystemsSWin TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysc_AuditSubsystemsKeyU TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptCatsD TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptCatsI TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptCatsS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptCatsSByAuSS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptCatsSByParent TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptCatsU TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTEmailCCD TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTEmailCCI TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTEmailCCS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTEmailCCSByQueryID TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTEmailCCU TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTEmailToD TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTEmailToI TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTEmailToS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTEmailToSByQueryID TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTEmailToU TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntitiesD TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntitiesDByList TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntitiesI TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntitiesS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntitiesU TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntityListCollS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntityListD TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntityListI TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntityListS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntityListU TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptEntityMembersS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptFilesD TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptFilesI TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptFilesS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTFilesSByAuSS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptFilesU TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptOpsD TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptOpsI TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptOpsS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptQueriesD TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptQueriesI TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptQueriesS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptQueriesU TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptQueryCollectionsS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RPTSelOpsS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptSMTPD TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptSMTPI TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptSMTPS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptSMTPU TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptStatementsByQueryS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptStatementsD TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptStatementsI TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptStatementsS TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usysr_RptStatementsU TO ReportUsers;
GRANT EXECUTE ON OBJECT::dbo.usys_BLDBVersionS TO ReportUsers;

-- ============================================================
-- Grant SELECT on Views
-- ============================================================
GRANT SELECT ON OBJECT::dbo.VIEW_ADA_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_ATA_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_EFS_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_FSA_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_LSA_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_RSM_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_TEMPXPA_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_XPA_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_CPA_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_GPA_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_LTA_REPORT_80 TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_NEL_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_NWA_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_RDA_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_TEMPAA8_DATA TO ReportUsers;
GRANT SELECT ON OBJECT::dbo.VIEW_AA8_DATA TO ReportUsers;

Step 3 — Map the Active Directory group to the SQL role:

After running the script, map your AD group to the ReportUsers role by running the following command, replacing DOMAIN\GroupName with your actual domain and group name:

ALTER ROLE ReportUsers ADD MEMBER [DOMAIN\GroupName];

Step 4 — Validate access:

1. Log in as a user who is a member of the AD group
2. Open the LT Auditor ^MP Reporting Console
3. Confirm the user can successfully access and run reports
4. If access is denied, verify:
   • The user is a member of the correct AD group
   • The AD group has been correctly mapped to the ReportUsers SQL role
   • The SQL script completed without errors

[Your administrator should confirm the correct domain and group name before running the mapping command, and retain a record of which AD group is mapped to the ReportUsers role for future reference.]