Index: /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs
===================================================================
--- /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs	(revision 15551)
+++ /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs	(revision 15552)
@@ -18,4 +18,42 @@
     }
 
+    public void DeleteByProjectId(Guid projectId) {
+      DataContext.ExecuteCommand(DeleteByProjectIdQueryString, projectId);
+    }
+
+    public void DeleteByProjectIdAndUserIds(Guid projectId, IEnumerable<Guid> userIds) {
+      string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramUserIds)) {
+        string queryString = string.Format(DeleteByProjectIdAndUserIdsQueryString, projectId, paramUserIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public void DeleteByProjectIdsAndUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> userIds) {
+      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
+      string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) {
+        string queryString = string.Format(DeleteByProjectIdsAndUserIdsQueryString, paramProjectIds, paramUserIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public void DeleteByProjectIdAndResourceIds(Guid projectId, IEnumerable<Guid> resourceIds) {
+      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(DeleteByProjectIdAndResourceIdsQueryString, projectId, paramResourceIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public void DeleteByProjectIdsAndResourceIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> resourceIds) {
+      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
+      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(DeleteByProjectIdsAndResourceIdsQueryString, paramProjectIds, paramResourceIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
     public bool CheckJobGrantedForResource(Guid jobId, Guid resourceId) {
       return DataContext.ExecuteQuery<int>(CheckJobGrantedForResourceQueryString, jobId, resourceId).First() > 0;
@@ -44,4 +82,53 @@
 
     #region String queries
+    private const string DeleteByProjectIdQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+	      (
+		      SELECT j.JobId
+		      FROM [Job] j
+		      WHERE j.ProjectId = {0}
+	      )
+    ";
+    private const string DeleteByProjectIdAndUserIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+	      (
+		      SELECT j.JobId
+		      FROM [Job] j
+		      WHERE j.ProjectId = {0}
+          AND j.OwnerUserId IN ({1})
+	      )
+    ";
+    private const string DeleteByProjectIdsAndUserIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+	      (
+		      SELECT j.JobId
+		      FROM [Job] j
+		      WHERE j.ProjectId IN ({0})
+          AND j.OwnerUserId IN ({1})
+	      )
+    ";
+    private const string DeleteByProjectIdAndResourceIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+	      (
+		      SELECT j.JobId
+		      FROM [Job] j
+		      WHERE j.ProjectId = {0}
+	      )
+      AND ajr.ResourceId IN ({1})
+    ";
+    private const string DeleteByProjectIdsAndResourceIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+	      (
+		      SELECT j.JobId
+		      FROM [Job] j
+		      WHERE j.ProjectId IN ({0})
+	      )
+      AND ajr.ResourceId IN ({1})
+    ";
     private const string CheckJobGrantedForResourceQueryString = @"
       WITH rbranch AS (
Index: /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs
===================================================================
--- /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs	(revision 15551)
+++ /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs	(revision 15552)
@@ -31,4 +31,8 @@
     public override Project GetById(Guid id) {
       return GetByIdQuery(DataContext, id);
+    }
+
+    public IEnumerable<Project> GetGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) {
+      return GetGrantedProjectsForUserQuery(DataContext, userAndGroupIds);
     }
 
@@ -63,7 +67,14 @@
          where project.ProjectId == projectId
          select project).SingleOrDefault());
+    private static readonly Func<DataContext, IEnumerable<Guid>, IEnumerable<Project>> GetGrantedProjectsForUserQuery =
+      CompiledQuery.Compile((DataContext db, IEnumerable<Guid> userAndGroupIds) =>
+      (from project in db.GetTable<Project>()
+       join projectPermission in db.GetTable<ProjectPermission>()
+       on project.ProjectId equals projectPermission.ProjectId
+       where userAndGroupIds.Contains(projectPermission.GrantedUserId)
+       select project).Distinct());
     #endregion
 
-    #region String queries
+      #region String queries
     private const string GetChildProjectsByIdQuery = @"
     	WITH ptree AS
Index: /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectPermissionDao.cs
===================================================================
--- /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectPermissionDao.cs	(revision 15551)
+++ /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectPermissionDao.cs	(revision 15552)
@@ -88,4 +88,10 @@
       AND pp.GrantedUserId IN ({1})
     ";
+    private const string GetGrantedProjectsForUserQueryString = @"
+      SELECT DISTINCT p.*
+      FROM [ProjectPermission] pp, [Project] p
+      WHERE pp.GrantedUserId IN ({0})
+      AND  pp.ProjectId = p.ProjectId
+    ";
     #endregion
   }
Index: /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Interfaces/IPersistenceManager.cs
===================================================================
--- /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Interfaces/IPersistenceManager.cs	(revision 15551)
+++ /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Interfaces/IPersistenceManager.cs	(revision 15552)
@@ -32,4 +32,5 @@
     #region Hive daos
     AssignedTaskResourceDao AssignedTaskResourceDao { get; }
+    AssignedJobResourceDao AssignedJobResourceDao { get; }
     AssignedProjectResourceDao AssignedProjectResourceDao { get; }
     DowntimeDao DowntimeDao { get; }
Index: /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Manager/PersistenceManager.cs
===================================================================
--- /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Manager/PersistenceManager.cs	(revision 15551)
+++ /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Manager/PersistenceManager.cs	(revision 15552)
@@ -41,4 +41,9 @@
     public AssignedTaskResourceDao AssignedTaskResourceDao {
       get { return assignedTaskResourceDao ?? (assignedTaskResourceDao = new AssignedTaskResourceDao(dataContext)); }
+    }
+
+    private AssignedJobResourceDao assignedJobResourceDao;
+    public AssignedJobResourceDao AssignedJobResourceDao {
+      get { return assignedJobResourceDao ?? (assignedJobResourceDao = new AssignedJobResourceDao(dataContext)); }
     }
 
Index: /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql
===================================================================
--- /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql	(revision 15551)
+++ /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql	(revision 15552)
@@ -36,4 +36,9 @@
   CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
   )
+CREATE TABLE [dbo].[AssignedJobResource](
+  [ResourceId] UniqueIdentifier NOT NULL,
+  [JobId] UniqueIdentifier NOT NULL,
+  CONSTRAINT [PK_dbo.ResourceIdJobId] PRIMARY KEY ([ResourceId], [JobId])
+  )
 CREATE TABLE [dbo].[Plugin](
   [PluginId] UniqueIdentifier NOT NULL,
@@ -73,10 +78,4 @@
   CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
   )
-CREATE TABLE [dbo].[ResourcePermission](
-  [ResourceId] UniqueIdentifier NOT NULL,
-  [GrantedUserId] UniqueIdentifier NOT NULL,
-  [GrantedByUserId] UniqueIdentifier NOT NULL,
-  CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])
-  )
 CREATE TABLE [dbo].[Task](
   [TaskId] UniqueIdentifier NOT NULL,
@@ -180,4 +179,8 @@
 ALTER TABLE [dbo].[AssignedTaskResource]
   ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
+ALTER TABLE [dbo].[AssignedJobResource]
+  ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+ALTER TABLE [dbo].[AssignedJobResource]
+  ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
 ALTER TABLE [dbo].[RequiredPlugins]
   ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
@@ -186,6 +189,4 @@
 ALTER TABLE [dbo].[Resource]
   ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
-ALTER TABLE [dbo].[ResourcePermission]
-  ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
 ALTER TABLE [dbo].[Task]
   ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
Index: /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql
===================================================================
--- /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql	(revision 15551)
+++ /branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql	(revision 15552)
@@ -42,4 +42,17 @@
 ALTER TABLE [dbo].[AssignedTaskResource]  DROP  CONSTRAINT [Resource_AssignedTaskResource]
 ALTER TABLE [dbo].[AssignedTaskResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY([ResourceId])
+REFERENCES [dbo].[Resource] ([ResourceId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Job_AssignedJobResource]
+ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY([JobId])
+REFERENCES [dbo].[Job] ([JobId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Resource_AssignedJobResource]
+ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY([ResourceId])
 REFERENCES [dbo].[Resource] ([ResourceId])
 ON UPDATE CASCADE
@@ -105,5 +118,5 @@
 REFERENCES [dbo].[Project] ([ProjectId])
 ON UPDATE CASCADE
-ON DELETE CASCADE
+-- ON DELETE CASCADE
 GO
 
Index: /branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/HiveService.cs
===================================================================
--- /branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/HiveService.cs	(revision 15551)
+++ /branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/HiveService.cs	(revision 15552)
@@ -69,4 +69,5 @@
 
     #region Task Methods
+    [Obsolete]
     public Guid AddTask(DT.Task task, DT.TaskData taskData, IEnumerable<Guid> resourceIds) {
       RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
@@ -353,4 +354,5 @@
       var pm = PersistenceManager;
       using (new PerformanceLogger("GetJobs")) {
+        // TODO-JAN: optimization potential - avoid using too many joins in linq
         var jobDao = pm.JobDao;
         var jobPermissionDao = pm.JobPermissionDao;
@@ -364,5 +366,7 @@
             .Select(x => x.ToDto())
             .ToList();
+          // calculate stats only for owned & permitted jobs; TODO: query only needed ones, not all
           var statistics = taskDao.GetAll()
+              .Where(x => jobs.Select(y => y.Id).Contains(x.JobId))
               .GroupBy(x => x.JobId)
               .Select(x => new {
@@ -393,4 +397,5 @@
     }
 
+    [Obsolete]
     public Guid AddJob(DT.Job jobDto) {
       RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
@@ -515,5 +520,6 @@
         var jobDao = pm.JobDao;
         pm.UseTransaction(() => {
-          // child task will be deleted by db-trigger
+          // child tasks will be deleted by db-trigger
+          // entries in AssignedJobResource will be deleted by foreign key clause CASCADE ON DELETE
           jobDao.Delete(jobId);
           pm.SubmitChanges();
@@ -728,9 +734,10 @@
     public void DeleteProject(Guid projectId) {
       RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator);
-      AuthorizationManager.AuthorizeForProjectAdministration(projectId);
       var pm = PersistenceManager;
       using (new PerformanceLogger("DeleteProject")) {
         var projectDao = pm.ProjectDao;
-        pm.UseTransaction(() => {
+        var assignedJobResourceDao = pm.AssignedJobResourceDao;
+        pm.UseTransaction(() => {
+          assignedJobResourceDao.DeleteByProjectId(projectId);
           projectDao.Delete(projectId);
           pm.SubmitChanges();
@@ -748,4 +755,5 @@
     }
 
+    // query granted projects for use (i.e. to calculate on)
     public IEnumerable<DT.Project> GetProjects() {
       RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
@@ -757,15 +765,19 @@
         var currentUserId = UserManager.CurrentUserId;
         return pm.UseTransaction(() => {
-          var projectPermissions = projectPermissionDao.GetAll();
-          return projectDao.GetAll().ToList()
-            .Where(x => isAdministrator
-              || x.OwnerUserId == currentUserId
-              || UserManager.VerifyUser(currentUserId, projectPermissions
-                  .Where(y => y.ProjectId == x.ProjectId)
-                  .Select(z => z.GrantedUserId)
-                  .ToList())
-              )
-            .Select(x => x.ToDto())
-            .ToList();
+          var userAndGroupIds = new List<Guid> { currentUserId };
+          userAndGroupIds.AddRange(UserManager.GetUserGroupIdsOfUser(currentUserId));
+          return projectDao.GetGrantedProjectsForUser(userAndGroupIds)
+            .Select(x => x.ToDto());
+          //var projectPermissions = projectPermissionDao.GetAll();
+          //return projectDao.GetAll().ToList()
+          //  .Where(x => isAdministrator
+          //    || x.OwnerUserId == currentUserId
+          //    || UserManager.VerifyUser(currentUserId, projectPermissions
+          //        .Where(y => y.ProjectId == x.ProjectId)
+          //        .Select(z => z.GrantedUserId)
+          //        .ToList())
+          //    )
+          //  .Select(x => x.ToDto())
+          //  .ToList();
         });
       }
@@ -816,10 +828,13 @@
         var projectPermissionDao = pm.ProjectPermissionDao;
         var projectDao = pm.ProjectDao;
+        var assignedJobResourceDao = pm.AssignedJobResourceDao;
         pm.UseTransaction(() => {
           if (cascading) {
             var childProjectIds = projectDao.GetChildProjectIdsById(projectId);
             projectPermissionDao.DeleteByProjectIdsAndGrantedUserIds(childProjectIds, grantedUserIds);
+            assignedJobResourceDao.DeleteByProjectIdsAndUserIds(childProjectIds, grantedUserIds);
           }
           projectPermissionDao.DeleteByProjectIdAndGrantedUserIds(projectId, grantedUserIds);
+          assignedJobResourceDao.DeleteByProjectIdAndUserIds(projectId, grantedUserIds);
           pm.SubmitChanges();
         });
@@ -829,4 +844,5 @@
     public IEnumerable<DT.ProjectPermission> GetProjectPermissions(Guid projectId) {
       RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
+      AuthorizationManager.AuthorizeForProjectAdministration(projectId);
       var pm = PersistenceManager;
       using (new PerformanceLogger("GetProjectPermissions")) {
@@ -880,4 +896,5 @@
       using (new PerformanceLogger("UnassignProjectResources")) {
         var assignedProjectResourceDao = pm.AssignedProjectResourceDao;
+        var assignedJobResourceDao = pm.AssignedJobResourceDao;
         var projectDao = pm.ProjectDao;
         pm.UseTransaction(() => {
@@ -885,12 +902,14 @@
             var childProjectIds = projectDao.GetChildProjectIdsById(projectId);
             assignedProjectResourceDao.DeleteByProjectIdsAndResourceIds(childProjectIds, resourceIds);
+            assignedJobResourceDao.DeleteByProjectIdsAndResourceIds(childProjectIds, resourceIds);
           }
           assignedProjectResourceDao.DeleteByProjectIdAndResourceIds(projectId, resourceIds);
-          pm.SubmitChanges();
-        });
-      }
-    }
-
-    public IEnumerable<AssignedProjectResource> GetAssignedResourcesForProject(Guid projectId) {
+          assignedJobResourceDao.DeleteByProjectIdAndResourceIds(projectId, resourceIds);
+          pm.SubmitChanges();
+        });
+      }
+    }
+
+    public IEnumerable<DT.AssignedProjectResource> GetAssignedResourcesForProject(Guid projectId) {
       RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
       var pm = PersistenceManager;
@@ -903,4 +922,5 @@
       }
     }
+
     #endregion
 
Index: /branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/Manager/AuthorizationManager.cs
===================================================================
--- /branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/Manager/AuthorizationManager.cs	(revision 15551)
+++ /branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/Manager/AuthorizationManager.cs	(revision 15552)
@@ -35,4 +35,5 @@
     private const string NOT_AUTHORIZED_USERRESOURCE = "Current user is not authorized to access the requested resource";
     private const string NOT_AUTHORIZED_USERPROJECT = "Current user is not authorized to access the requested project";
+    private const string NOT_AUTHORIZED_USERJOB = "Current user is not authorized to access the requested job";
     private const string NOT_AUTHORIZED_PROJECTRESOURCE = "Selected project is not authorized to access the requested resource";
 
@@ -169,5 +170,5 @@
       if (permission == Permission.NotAllowed
           || ((permission != requiredPermissionEntity) && requiredPermissionEntity == Permission.Full)) {
-        throw new SecurityException(NOT_AUTHORIZED_USERRESOURCE);
+        throw new SecurityException(NOT_AUTHORIZED_USERJOB);
       }
     }
