Saturday, July 3, 2010

Checking current user Menu Rights

SQL QUERY :

SET NOCOUNT ON

DECLARE @CurrentUserTypeID INT = 1 ------ <---- Pass Current User Type

-----------------------------------
DECLARE @tblUserType TABLE
(
UserTypeID INT
, TypeName VARCHAR(50)
, MenuRights BIGINT
)

INSERT INTO @tblUserType
VALUES (1, 'Admin', 1)
, (2, 'GenralManager', 2)
, (3, 'SalesManager', 4)
, (4, 'SalesMan', 8)
, (5, 'Client', 16)

DECLARE @tblMenu TABLE
(
MenuID INT
, MenuName VARCHAR(50)
, MenuRights BIGINT
)

INSERT INTO @tblMenu
VALUES (1, 'Admin Setting', 1)
, (2, 'Genral Setting', 3)
, (3, 'Sales Report', 7)
, (4, 'Sales Order Folloup', 15)
, (5, 'Client FeedBack', 31)

DECLARE @UserRights BIGINT = 0

SELECT @UserRights = MenuRights
FROM @tblUserType
WHERE UserTypeID = @CurrentUserTypeID

SELECT *
FROM @tblMenu
WHERE MenuRights & @UserRights = @UserRights


Admin Menu List :
MenuID MenuName MenuRights
----------- -------------------------------------------------- --------------------
1 Admin Setting 1
2 Genral Setting 3
3 Sales Report 7
4 Sales Order Folloup 15
5 Client FeedBack 31


GenralManager Menu List :
MenuID MenuName MenuRights
----------- -------------------------------------------------- --------------------
2 Genral Setting 3
3 Sales Report 7
4 Sales Order Folloup 15
5 Client FeedBack 31


SalesManager Menu List :
MenuID MenuName MenuRights
----------- -------------------------------------------------- --------------------
3 Sales Report 7
4 Sales Order Folloup 15
5 Client FeedBack 31

No comments: