/*    ==Scripting Parameters==

    Source Server Version : SQL Server 2016 (13.0.1742)
    Source Database Engine Edition : Microsoft SQL Server Enterprise Edition
    Source Database Engine Type : Standalone SQL Server

    Target Server Version : SQL Server 2016
    Target Database Engine Edition : Microsoft SQL Server Enterprise Edition
    Target Database Engine Type : Standalone SQL Server
*/

USE [IDA]
GO

/****** Object:  View [dbo].[UserRights]    Script Date: 16.11.2017 7:54:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[UserRights]
AS
SELECT rights.UserId,
       access_rights.*,
       folder.fullpath AS Path
FROM   folder
       JOIN(SELECT usergroupclient.client_id   AS ClientId,
                   folderaccessright.FolderId,
                   Max(Cast([read] AS INT))    AS [Read],
                   Max(Cast([move] AS INT))    AS [Move],
                   Max(Cast([create] AS INT))  AS [Create],
                   Max(Cast([update] AS INT))  AS [Update],
                   Max(Cast([control] AS INT)) AS [Control],
                   Max(Cast([delete] AS INT))  AS [Delete]
            FROM   usergroupclient
                   JOIN folderaccessright
                     ON folderaccessright.usergroupid =
                        usergroupclient.usergroup_id
            GROUP  BY usergroupclient.client_id,
                      folderaccessright.folderid
            UNION ALL
            SELECT -852 AS ClientId,
                   id   AS FolderId,
                   1    AS [Read],
                   1    AS [Move],
                   1    AS [Create],
                   1    AS [Update],
                   1    AS [Control],
                   1    AS [Delete]
            FROM   folder) access_rights
         ON folder.id = access_rights.folderid
       JOIN (SELECT usergroupuser.user_id       AS UserId,
                    folderaccessright.folderid,
                    Max(Cast([read] AS INT))    AS [Read],
                    Max(Cast([move] AS INT))    AS [Move],
                    Max(Cast([create] AS INT))  AS [Create],
                    Max(Cast([update] AS INT))  AS [Update],
                    Max(Cast([control] AS INT)) AS [Control],
                    Max(Cast([delete] AS INT))  AS [Delete]
             FROM   usergroupuser
                    JOIN folderaccessright
                      ON folderaccessright.usergroupid =
                         usergroupuser.usergroup_id
             GROUP  BY usergroupuser.user_id,
                       folderaccessright.folderid
             UNION ALL
             SELECT -874 AS UserId,
                    id   AS FolderId,
                    1    AS [Read],
                    1    AS [Move],
                    1    AS [Create],
                    1    AS [Update],
                    1    AS [Control],
                    1    AS [Delete]
             FROM   folder) rights
         ON folder.id = rights.folderid
GO