User_Space_Permissions

Definition

CREATE   VIEW
  [sec].[User_Space_Permissions] AS
SELECT
  [u].[User_Id],
  [u].[Name],
  [u].[Login],
  [u].[Email],
  [u].[Description],
  CAST([u].[Created] AS DATE) [Created_Date],
  [u].[Is_Deleted],
  CASE [ro].[Name]
    WHEN 'enum.role.viewer.name' THEN 'Viewer'
    WHEN 'enum.role.contributor.name' THEN 'Contributor'
    WHEN 'enum.role.admin.name' THEN 'Admin'
  END [Role],
  [u].[Is_Group] [Is_Group],
  [s].[Name] [Space],
  CASE
    WHEN [ro].[Name] = 'enum.role.contributor.name' THEN (
      SELECT
        COUNT(*)
      FROM
        [dbo].[APP_Applied_Permission] [p]
      WHERE
        [p].[User_Id] = [u].[User_Id]
        AND [p].[Permission_Type_Id] = 140
        AND [p].[Ref_Object_Id] = [s].[Space_Id]
    )
    WHEN [ro].[Name] = 'enum.role.admin.name' THEN 1
    WHEN [ro].[Name] = 'enum.role.viewer.name' THEN 0
  END [Space_Write_Persmission]
FROM
  [dbo].[APP_User] [u]
  LEFT JOIN [dbo].[APP_Role] [ro]  ON [ro].[Role_Id] = [u].[Role_Id]
  CROSS JOIN [dbo].[MR_Space] [s]
WHERE
  [u].[Is_Group] = 0

Columns

#	Column	Data Type	Nullable	Source
1	User_Id	int	No	APP_User.User_Id
2	Name	nvarchar	No	APP_User.Name
3	Login	nvarchar	Yes	APP_User.Login
4	Email	varchar	Yes	APP_User.Email
5	Description	nvarchar	Yes	APP_User.Description
6	Created_Date	date	Yes	APP_User.Created
7	Is_Deleted	bit	No	APP_User.Is_Deleted
8	Role	varchar	Yes	APP_Role.Name
9	Is_Group	bit	No	APP_User.Is_Group
10	Space	nvarchar	No	MR_Space.Name
11	Space_Write_Persmission	int	Yes	APP_Role.Name

graph LR
  n1539["User_Space_Permissions"]
  n1260["MR_Space"]
  n1262["APP_Role"]
  n1288["APP_User"]
  n1260 --> n1539
  n1262 --> n1539
  n1288 --> n1539
  style n1539 fill:#00A081,stroke:#008068,color:#fff
  click n1539 href "/actual/userspacepermissions/"
  click n1260 href "/actual/mrspace/"
  click n1262 href "/actual/approle/"
  click n1288 href "/actual/appuser/"