locked
How to give access right for each menu to each user RRS feed

  • Question

  • Hi I have the following table and it's records.  How can write the insert statement to insert each menus to each active users. 

    Please can you help with sql  or procedure how to write the insert statement 

    create table Appusers
    (id int ,Name vachar(max))
    
    Create table AppMenu
    (MenuId int, MenuName varchar(max), IsActive bit)
    
    Users table
    id       Name        IsAacrtive
    1        AAA           1
    2        BBB           1
    3        CCC           0
    
    
    Create table UserAcesss
    (UserId  int, MenuName varchar(max), Accessright varchar(1)
    
    AppMenu table
    
    MenuId      Name                      
    1            Customer Create           
    2            Customer Delete           
    3            Customer Modify           
    4            Supplier Create           
    5            Supplier Delete           
    6            Supplier Modify           
    
    
    
    
    Result
    
    UserId     MenuName                 Accessright
    1          Customer Create           Y
    1          Customer Delete           Y
    1          Customer Modify           Y
    1          Supplier Create           Y
    1          Supplier Delete           Y
    1          Supplier Modify           Y
    
    2          Customer Create           Y
    2          Customer Delete           Y
    2          Customer Modify           Y
    2          Supplier Create           Y
    2          Supplier Delete           Y
    2          Supplier Modify           Y

    With Many Thanks

    Pol


    polachan

    Tuesday, April 16, 2019 6:51 PM

Answers

  • Sorry for  my question is not clear on the description . I am trying to insert each record from user and from menu to the table useraccess 

    INSERT INTO AppMenu (MenuId,MenuName,IsActive)
    SELECT m.MenuId,
    	u.Name,
    	'Y'
    FROM AppMenu m
    CROSS JOIN Appusers u
    WHERE IsActive = 1;


    Wednesday, April 17, 2019 12:44 AM
  • Hi  polachan,

    You could refer to above suggestions or use below query which is similar to above 

    create table Appusers
    (id int ,Name varchar(max), IsActive bit)
    
    insert into Appusers values 
    (1,        'AAA',           1),
    (2,        'BBB',           1),
    (3,        'CCC',           0)
    
    
    
    
    
    Create table AppMenu
    (MenuId int, MenuName varchar(max))
    insert into appmenu  values                    
    (1,            'Customer Create' ),          
    (2,            'Customer Delete' ),          
    (3,            'Customer Modify' ),          
    (4,            'Supplier Create' ),          
    (5,            'Supplier Delete'),           
    (6,            'Supplier Modify')           
    
    Create   table UserAcesss
    (UserId  int, MenuName varchar(max), Accessright varchar(1))
    ;with cte1  as (  select id  ,Name , IsActive from appusers  where IsActive=1
    )
    
    insert into UserAcesss (UserId,MenuName,Accessright)
    select a.id,b.menuname, 'Y' as accessright from cte1 a cross join appmenu b 
    
    select *  from UserAcesss
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by polachan Wednesday, April 17, 2019 10:29 AM
    Wednesday, April 17, 2019 2:04 AM

All replies

  • You can leverage a CROSS JOIN for this.

    SELECT m.MenuId,
    	u.Name,
    	'Y'
    FROM AppMenu m
    CROSS JOIN Appusers u
    WHERE IsActive = 1;
    


    Tuesday, April 16, 2019 7:07 PM
  • DECLARE @AppUsers TABLE (
    	UserId int, 
    	Name varchar(max),
    	IsActive bit
    );
    INSERT INTO @AppUsers VALUES
    (1, 'AAA', 1), (2, 'BBB', 1), (3, 'CC', 0);
    
    DECLARE @AppMenu TABLE (
    	MenuId int, 
    	MenuName varchar(max)
    );
    INSERT INTO @AppMenu VALUES
    (1, 'Customer Create'), 
    (2, 'Customer Delete'), 
    (3, 'Customer Modify'), 
    (4, 'Supplier Create'), 
    (5, 'Supplier Delete'),
    (6, 'Supplier Modify');
    
    SELECT u.UserId, m.MenuName, 'Y' AS AccessRight
    FROM @AppUsers AS u, @AppMenu AS m
    WHERE u.IsActive = 1;


    A Fan of SSIS, SSRS and SSAS

    Tuesday, April 16, 2019 7:12 PM
  • It will not work.

    I want to insert all  record into the table useraccess using for loop    for each users and for each menu  as given below

    UserId     MenuName                 Accessright
    1          Customer Create           Y
    1          Customer Delete           Y
    1          Customer Modify           Y
    1          Supplier Create           Y
    1          Supplier Delete           Y
    1          Supplier Modify           Y
    
    2          Customer Create           Y
    2          Customer Delete           Y
    2          Customer Modify           Y
    2          Supplier Create           Y
    2          Supplier Delete           Y
    2          Supplier Modify           Y


    polachan

    Tuesday, April 16, 2019 8:47 PM
  • Sorry for  my question is not clear on the description . I am trying to insert each record from user and from menu to the table useraccess 

    UserId     MenuName                 Accessright
    1          Customer Create           Y
    1          Customer Delete           Y
    1          Customer Modify           Y
    1          Supplier Create           Y
    1          Supplier Delete           Y
    1          Supplier Modify           Y
    
    2          Customer Create           Y
    2          Customer Delete           Y
    2          Customer Modify           Y
    2          Supplier Create           Y
    2          Supplier Delete           Y
    2          Supplier Modify           Y


    polachan

    Tuesday, April 16, 2019 8:48 PM
  • INSERT INTO UserAccess
    SELECT u.UserId, m.MenuName, 'Y' AS AccessRight
    FROM AppUsers AS u, AppMenu AS m
    WHERE u.IsActive = 1;


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by polachan Wednesday, April 17, 2019 10:29 AM
    • Unmarked as answer by polachan Wednesday, April 17, 2019 10:29 AM
    Tuesday, April 16, 2019 9:01 PM
  • Sorry for  my question is not clear on the description . I am trying to insert each record from user and from menu to the table useraccess 

    INSERT INTO AppMenu (MenuId,MenuName,IsActive)
    SELECT m.MenuId,
    	u.Name,
    	'Y'
    FROM AppMenu m
    CROSS JOIN Appusers u
    WHERE IsActive = 1;


    Wednesday, April 17, 2019 12:44 AM
  • Hi  polachan,

    You could refer to above suggestions or use below query which is similar to above 

    create table Appusers
    (id int ,Name varchar(max), IsActive bit)
    
    insert into Appusers values 
    (1,        'AAA',           1),
    (2,        'BBB',           1),
    (3,        'CCC',           0)
    
    
    
    
    
    Create table AppMenu
    (MenuId int, MenuName varchar(max))
    insert into appmenu  values                    
    (1,            'Customer Create' ),          
    (2,            'Customer Delete' ),          
    (3,            'Customer Modify' ),          
    (4,            'Supplier Create' ),          
    (5,            'Supplier Delete'),           
    (6,            'Supplier Modify')           
    
    Create   table UserAcesss
    (UserId  int, MenuName varchar(max), Accessright varchar(1))
    ;with cte1  as (  select id  ,Name , IsActive from appusers  where IsActive=1
    )
    
    insert into UserAcesss (UserId,MenuName,Accessright)
    select a.id,b.menuname, 'Y' as accessright from cte1 a cross join appmenu b 
    
    select *  from UserAcesss
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by polachan Wednesday, April 17, 2019 10:29 AM
    Wednesday, April 17, 2019 2:04 AM