Stored Procedure To Search the AccessRights given to the Users.
- by thevan
Hi,
I want to display the Access Rights given to the Users for the particular module.
I have Seven Tables such as RoleAccess, Roles, Functions, Module, SubModule, Company and Unit. RoleAccess is the Main Table. The AccessRights given will be stored in the RoleAccess Table only.
RoleAccess Table has the following columns such as RoleID, CompanyID, UnitID, FunctionID, ModuleID, SubModuleID, Create, Update, Delete, Read, Approve. Here Create_f, Update_f, Delete_f, Read_f and Approve_f are flags.
Company Table has two columns such as CompanyID and CompanyName.
Unit Table has three columns such as UnitID, UnitName and CompanyID.
Roles Table has four columns such as RoleID, RoleName, CompanyID and UnitID.
Module Table has two columns such as ModuleID and ModuleName.
SubModule Table has three columns such as ModuleID, SubModuleID, SubModuleName.
Functions Table has five columns such as FunctionID, FunctionName, ModuleID and SubModuleID.
At First, The RoleAccess Table does not contain any records.
So I want to display the ModuleName, SubModuleName, FunctionName, CompanyID, RoleID, UnitID, FunctionID, ModuleID, SubModuleID, Create_f, Update_f, Delete_f, Read_f and Approve_f.
If the AccessRights is assigned to the Particular RoleID means the flags in the search results will be 1 else it will be 0.
I have witten one stored procedure but it displays the records based on the RoleID stored in the RoleAccess table. But I also want to display the Flags as 0 for the Roles not stored in the RoleAccess Table.
I want the Stored Procedure for this. Any one please help me.
