IF DB_NAME()='master' BEGIN RAISERROR(N'Unable to execute script on master database, USE a database before running this script',16,1) RETURN END GO DECLARE @schema varchar(max) SET @schema = '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' + '' SET @schema = @schema + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' + '' + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' + '' + '' SET @schema = @schema + '' update Configuration SET SchemaDefinition = @schema WHERE EngineVersion = 1 GO SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON /* AudioCaptioning */ CREATE TABLE [Tmp_AudioCaptioning]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_AudioCaptioning] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_AudioCaptioning ON IF EXISTS(SELECT * FROM dbo.AudioCaptioning) BEGIN INSERT INTO dbo.Tmp_AudioCaptioning (Id, Name) SELECT Id, Name FROM dbo.AudioCaptioning WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_AudioCaptioning OFF GO ALTER TABLE dbo.UserItem DROP CONSTRAINT FK_UserItem_AudioCaptioning GO DROP TABLE dbo.AudioCaptioning GO EXECUTE sp_rename N'dbo.Tmp_AudioCaptioning', N'AudioCaptioning', 'OBJECT' GO COMMIT GO ALTER TABLE dbo.UserItem ADD CONSTRAINT FK_UserItem_AudioCaptioning FOREIGN KEY ( AudioCaptioning) REFERENCES dbo.AudioCaptioning ( Id) GO /* End AudioCaptioning */ /* CompletionStatus */ CREATE TABLE [Tmp_CompletionStatus]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_CompletionStatus] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_CompletionStatus ON IF EXISTS(SELECT * FROM dbo.CompletionStatus) BEGIN INSERT INTO dbo.Tmp_CompletionStatus (Id, Name) SELECT Id, Name FROM dbo.CompletionStatus WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_CompletionStatus OFF GO ALTER TABLE [ActivityAttemptItem] DROP CONSTRAINT FK_ActivityAttemptItem_CompletionStatus GO ALTER TABLE [AttemptItem] DROP CONSTRAINT FK_AttemptItem_CompletionStatus go ALTER TABLE dbo.AttemptObjectiveItem DROP CONSTRAINT FK_AttemptObjectiveItem_CompletionStatus GO DROP TABLE dbo.CompletionStatus GO EXECUTE sp_rename N'dbo.Tmp_CompletionStatus', N'CompletionStatus', 'OBJECT' GO COMMIT GO ALTER TABLE [ActivityAttemptItem] ADD CONSTRAINT FK_ActivityAttemptItem_CompletionStatus FOREIGN KEY ([CompletionStatus]) REFERENCES [CompletionStatus] (Id) GO ALTER TABLE [AttemptItem] ADD CONSTRAINT FK_AttemptItem_CompletionStatus FOREIGN KEY ([CompletionStatus]) REFERENCES [CompletionStatus] (Id) go ALTER TABLE [AttemptObjectiveItem] ADD CONSTRAINT FK_AttemptObjectiveItem_CompletionStatus FOREIGN KEY ([CompletionStatus]) REFERENCES [CompletionStatus] (Id) GO /* End CompletionStatus */ /* ExitMode */ CREATE TABLE [Tmp_ExitMode]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_ExitMode] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_ExitMode ON IF EXISTS(SELECT * FROM dbo.ExitMode) BEGIN INSERT INTO dbo.Tmp_ExitMode (Id, Name) SELECT Id, Name FROM dbo.ExitMode WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_ExitMode OFF GO ALTER TABLE dbo.ActivityAttemptItem DROP CONSTRAINT FK_ActivityAttemptItem_Exit GO DROP TABLE dbo.ExitMode GO EXECUTE sp_rename N'dbo.Tmp_ExitMode', N'ExitMode', 'OBJECT' GO COMMIT GO ALTER TABLE [ActivityAttemptItem] ADD CONSTRAINT FK_ActivityAttemptItem_Exit FOREIGN KEY ([Exit]) REFERENCES [ExitMode] (Id) GO /* End ExitMode */ /* DisplayMode */ CREATE TABLE [Tmp_DisplayMode]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_DisplayMode] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_DisplayMode ON IF EXISTS(SELECT * FROM dbo.DisplayMode) BEGIN INSERT INTO dbo.Tmp_DisplayMode (Id, Name) SELECT Id, Name FROM dbo.DisplayMode WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_DisplayMode OFF GO DROP TABLE dbo.DisplayMode GO EXECUTE sp_rename N'dbo.Tmp_DisplayMode', N'DisplayMode', 'OBJECT' GO COMMIT GO /* End DisplayMode */ /* SuccessStatus */ CREATE TABLE [Tmp_SuccessStatus]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_SuccessStatus] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_SuccessStatus ON IF EXISTS(SELECT * FROM dbo.SuccessStatus) BEGIN INSERT INTO dbo.Tmp_SuccessStatus (Id, Name) SELECT Id, Name FROM dbo.SuccessStatus WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_SuccessStatus OFF GO ALTER TABLE [ActivityAttemptItem] DROP CONSTRAINT FK_ActivityAttemptItem_SuccessStatus GO ALTER TABLE [PackageGlobalObjectiveItem] DROP CONSTRAINT FK_PackageGlobalObjectiveItem_SuccessStatus GO ALTER TABLE [AttemptItem] DROP CONSTRAINT FK_AttemptItem_SuccessStatus GO ALTER TABLE [AttemptObjectiveItem] DROP CONSTRAINT FK_AttemptObjectiveItem_SuccessStatus GO ALTER TABLE [LearnerGlobalObjectiveItem] DROP CONSTRAINT FK_LearnerGlobalObjectiveItem_SuccessStatus GO DROP TABLE dbo.SuccessStatus GO EXECUTE sp_rename N'dbo.Tmp_SuccessStatus', N'SuccessStatus', 'OBJECT' GO COMMIT GO ALTER TABLE [ActivityAttemptItem] ADD CONSTRAINT FK_ActivityAttemptItem_SuccessStatus FOREIGN KEY ([SuccessStatus]) REFERENCES [SuccessStatus] (Id) GO ALTER TABLE [PackageGlobalObjectiveItem] ADD CONSTRAINT FK_PackageGlobalObjectiveItem_SuccessStatus FOREIGN KEY ([SuccessStatus]) REFERENCES [SuccessStatus] (Id) GO ALTER TABLE [AttemptItem] ADD CONSTRAINT FK_AttemptItem_SuccessStatus FOREIGN KEY ([SuccessStatus]) REFERENCES [SuccessStatus] (Id) GO ALTER TABLE [AttemptObjectiveItem] ADD CONSTRAINT FK_AttemptObjectiveItem_SuccessStatus FOREIGN KEY ([SuccessStatus]) REFERENCES [SuccessStatus] (Id) GO ALTER TABLE [LearnerGlobalObjectiveItem] ADD CONSTRAINT FK_LearnerGlobalObjectiveItem_SuccessStatus FOREIGN KEY ([SuccessStatus]) REFERENCES [SuccessStatus] (Id) GO /* End SuccessStatus */ /* TimeLimitAction */ CREATE TABLE [Tmp_TimeLimitAction]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_TimeLimitAction] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_TimeLimitAction ON IF EXISTS(SELECT * FROM dbo.TimeLimitAction) BEGIN INSERT INTO dbo.Tmp_TimeLimitAction (Id, Name) SELECT Id, Name FROM dbo.TimeLimitAction WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_TimeLimitAction OFF GO ALTER TABLE [ActivityPackageItem] DROP CONSTRAINT FK_ActivityPackageItem_TimeLimitAction GO DROP TABLE dbo.TimeLimitAction GO EXECUTE sp_rename N'dbo.Tmp_TimeLimitAction', N'TimeLimitAction', 'OBJECT' GO COMMIT GO ALTER TABLE [ActivityPackageItem] ADD CONSTRAINT FK_ActivityPackageItem_TimeLimitAction FOREIGN KEY ([TimeLimitAction]) REFERENCES [TimeLimitAction] (Id) GO /* End TimeLimitAction */ /* InteractionType */ CREATE TABLE [Tmp_InteractionType]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_InteractionType] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_InteractionType ON IF EXISTS(SELECT * FROM dbo.InteractionType) BEGIN INSERT INTO dbo.Tmp_InteractionType (Id, Name) SELECT Id, Name FROM dbo.InteractionType WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_InteractionType OFF GO ALTER TABLE [InteractionItem] DROP CONSTRAINT FK_InteractionItem_InteractionType GO DROP TABLE dbo.InteractionType GO EXECUTE sp_rename N'dbo.Tmp_InteractionType', N'InteractionType', 'OBJECT' GO COMMIT GO ALTER TABLE [InteractionItem] ADD CONSTRAINT FK_InteractionItem_InteractionType FOREIGN KEY ([InteractionType]) REFERENCES [InteractionType] (Id) GO /* End InteractionType */ /* InteractionResultState */ CREATE TABLE [Tmp_InteractionResultState]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_InteractionResultState] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_InteractionResultState ON IF EXISTS(SELECT * FROM dbo.InteractionResultState) BEGIN INSERT INTO dbo.Tmp_InteractionResultState (Id, Name) SELECT Id, Name FROM dbo.InteractionResultState WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_InteractionResultState OFF GO ALTER TABLE [InteractionItem] DROP CONSTRAINT FK_InteractionItem_ResultState GO DROP TABLE dbo.InteractionResultState GO EXECUTE sp_rename N'dbo.Tmp_InteractionResultState', N'InteractionResultState', 'OBJECT' GO COMMIT GO ALTER TABLE [InteractionItem] ADD CONSTRAINT FK_InteractionItem_ResultState FOREIGN KEY ([ResultState]) REFERENCES [InteractionResultState] (Id) GO /* End InteractionResultState */ /* PackageFormat */ CREATE TABLE [Tmp_PackageFormat]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_PackageFormat] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_PackageFormat ON IF EXISTS(SELECT * FROM dbo.PackageFormat) BEGIN INSERT INTO dbo.Tmp_PackageFormat (Id, Name) SELECT Id, Name FROM dbo.PackageFormat WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_PackageFormat OFF GO ALTER TABLE [PackageItem] DROP CONSTRAINT FK_PackageItem_PackageFormat GO DROP TABLE dbo.PackageFormat GO EXECUTE sp_rename N'dbo.Tmp_PackageFormat', N'PackageFormat', 'OBJECT' GO COMMIT GO ALTER TABLE [PackageItem] ADD CONSTRAINT FK_PackageItem_PackageFormat FOREIGN KEY ([PackageFormat]) REFERENCES [PackageFormat] (Id) GO /* End PackageFormat */ /* LessonStatus */ CREATE TABLE [Tmp_LessonStatus]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_LessonStatus] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_LessonStatus ON IF EXISTS(SELECT * FROM dbo.LessonStatus) BEGIN INSERT INTO dbo.Tmp_LessonStatus (Id, Name) SELECT Id, Name FROM dbo.LessonStatus WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_LessonStatus OFF GO ALTER TABLE [AttemptObjectiveItem] DROP CONSTRAINT FK_AttemptObjectiveItem_LessonStatus GO ALTER TABLE [ActivityAttemptItem] DROP CONSTRAINT FK_ActivityAttemptItem_LessonStatus GO DROP TABLE dbo.LessonStatus GO EXECUTE sp_rename N'dbo.Tmp_LessonStatus', N'LessonStatus', 'OBJECT' GO COMMIT GO ALTER TABLE [AttemptObjectiveItem] ADD CONSTRAINT FK_AttemptObjectiveItem_LessonStatus FOREIGN KEY ([LessonStatus]) REFERENCES [LessonStatus] (Id) GO ALTER TABLE [ActivityAttemptItem] ADD CONSTRAINT FK_ActivityAttemptItem_LessonStatus FOREIGN KEY ([LessonStatus]) REFERENCES [LessonStatus] (Id) GO /* End LessonStatus */ /* NavigationCommand */ CREATE TABLE [Tmp_NavigationCommand]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_NavigationCommand] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_NavigationCommand ON IF EXISTS(SELECT * FROM dbo.NavigationCommand) BEGIN INSERT INTO dbo.Tmp_NavigationCommand (Id, Name) SELECT Id, Name FROM dbo.NavigationCommand WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_NavigationCommand OFF GO ALTER TABLE [SequencingLogEntryItem] DROP CONSTRAINT FK_SequencingLogEntryItem_NavigationCommand GO DROP TABLE dbo.NavigationCommand GO EXECUTE sp_rename N'dbo.Tmp_NavigationCommand', N'NavigationCommand', 'OBJECT' GO COMMIT GO ALTER TABLE [SequencingLogEntryItem] ADD CONSTRAINT FK_SequencingLogEntryItem_NavigationCommand FOREIGN KEY ([NavigationCommand]) REFERENCES [NavigationCommand] (Id) GO /* End NavigationCommand */ /* SequencingEventType */ CREATE TABLE [Tmp_SequencingEventType]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_SequencingEventType] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_SequencingEventType ON IF EXISTS(SELECT * FROM dbo.SequencingEventType) BEGIN INSERT INTO dbo.Tmp_SequencingEventType (Id, Name) SELECT Id, Name FROM dbo.SequencingEventType WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_SequencingEventType OFF GO ALTER TABLE [SequencingLogEntryItem] DROP CONSTRAINT FK_SequencingLogEntryItem_EventType GO DROP TABLE dbo.SequencingEventType GO EXECUTE sp_rename N'dbo.Tmp_SequencingEventType', N'SequencingEventType', 'OBJECT' GO COMMIT GO ALTER TABLE [SequencingLogEntryItem] ADD CONSTRAINT FK_SequencingLogEntryItem_EventType FOREIGN KEY ([EventType]) REFERENCES [SequencingEventType] (Id) GO /* End SequencingEventType */ /* AttemptStatus */ CREATE TABLE [Tmp_AttemptStatus]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_AttemptStatus] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_AttemptStatus ON IF EXISTS(SELECT * FROM dbo.AttemptStatus) BEGIN INSERT INTO dbo.Tmp_AttemptStatus (Id, Name) SELECT Id, Name FROM dbo.AttemptStatus WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_AttemptStatus OFF GO ALTER TABLE [LearnerAssignmentItem] DROP CONSTRAINT FK_LearnerAssignmentItem_NonELearningStatus GO DROP INDEX AttemptItem.LearnerAssignmentIndex GO ALTER TABLE [AttemptItem] DROP CONSTRAINT FK_AttemptItem_AttemptStatus GO DROP TABLE dbo.AttemptStatus GO EXECUTE sp_rename N'dbo.Tmp_AttemptStatus', N'AttemptStatus', 'OBJECT' GO COMMIT GO ALTER TABLE [LearnerAssignmentItem] ADD CONSTRAINT FK_LearnerAssignmentItem_NonELearningStatus FOREIGN KEY ([NonELearningStatus]) REFERENCES [AttemptStatus] (Id) GO CREATE INDEX LearnerAssignmentIndex ON AttemptItem(LearnerAssignmentId) INCLUDE (AttemptStatus) GO ALTER TABLE [AttemptItem] ADD CONSTRAINT FK_AttemptItem_AttemptStatus FOREIGN KEY ([AttemptStatus]) REFERENCES [AttemptStatus] (Id) GO /* End AttemptStatus */ /* LearnerAssignmentState */ CREATE TABLE [Tmp_LearnerAssignmentState]( Id int IDENTITY PRIMARY KEY, Name varchar(63) NOT NULL ) GO GRANT SELECT ON [Tmp_LearnerAssignmentState] TO LearningStore GO BEGIN TRANSACTION SET IDENTITY_INSERT dbo.Tmp_LearnerAssignmentState ON IF EXISTS(SELECT * FROM dbo.LearnerAssignmentState) BEGIN INSERT INTO dbo.Tmp_LearnerAssignmentState (Id, Name) SELECT Id, Name FROM dbo.LearnerAssignmentState WITH (HOLDLOCK TABLOCKX) END SET IDENTITY_INSERT dbo.Tmp_LearnerAssignmentState OFF GO ALTER TABLE dbo.UserItem DROP CONSTRAINT FK_UserItem_AudioCaptioning GO DROP TABLE dbo.LearnerAssignmentState GO EXECUTE sp_rename N'dbo.Tmp_LearnerAssignmentState', N'LearnerAssignmentState', 'OBJECT' GO COMMIT GO ALTER TABLE dbo.UserItem ADD CONSTRAINT FK_UserItem_AudioCaptioning FOREIGN KEY ( AudioCaptioning) REFERENCES dbo.AudioCaptioning ( Id) GO /* End LearnerAssignmentState */ ALTER TABLE LearnerAssignmentItem ADD [GuidId] uniqueidentifier DEFAULT (newid()) ROWGUIDCOL GO --Give GuidId to existing data CREATE TABLE TempItem (Id bigint, GuidId uniqueidentifier DEFAULT (newid())) INSERT INTO TempItem (Id) SELECT Id FROM LearnerAssignmentItem WHERE GuidId IS NULL UPDATE LearnerAssignmentItem SET GuidId = ti.GuidID FROM LearnerAssignmentItem lai INNER JOIN TempItem ti on ti.Id = lai.Id DROP TABLE TempItem GO -- ALTER FUNCTION for the delete security on the AssignmentItem item type ALTER FUNCTION [AssignmentItem$DeleteSecurity](@UserKey nvarchar(250),@Id bigint) RETURNS bit AS BEGIN RETURN ( CASE WHEN EXISTS ( SELECT * FROM InstructorAssignmentItem iai INNER JOIN UserItem ui ON iai.InstructorId=ui.Id WHERE iai.AssignmentId=@Id AND ui.[Key]=@UserKey ) THEN 1 ELSE 0 END ) | ( CASE WHEN EXISTS ( SELECT * FROM AssignmentItem ai INNER JOIN UserItem ui ON ai.CreatedBy=ui.Id WHERE ai.Id=@Id AND ui.[Key]=@UserKey ) THEN 1 ELSE 0 END ) END GO GRANT EXECUTE ON [AssignmentItem$DeleteSecurity] TO LearningStore GO -- Create a function that implements the SeqNavOrganizationGlobalObjectiveView view ALTER FUNCTION [SeqNavOrganizationGlobalObjectiveView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT GlobalObjectiveItem.OrganizationId OrganizationId, GlobalObjectiveItem.[Key] [Key], PackageGlobalObjectiveItem.LearnerId LearnerId, PackageGlobalObjectiveItem.ScaledScore ScaledScore, PackageGlobalObjectiveItem.SuccessStatus SuccessStatus FROM GlobalObjectiveItem INNER JOIN PackageGlobalObjectiveItem ON PackageGlobalObjectiveItem.GlobalObjectiveId=GlobalObjectiveItem.Id ) GO GRANT SELECT ON [SeqNavOrganizationGlobalObjectiveView] TO LearningStore GO -- Create a function that implements the SeqNavLearnerGlobalObjectiveView view ALTER FUNCTION [SeqNavLearnerGlobalObjectiveView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT LearnerGlobalObjectiveItem.LearnerId LearnerId, GlobalObjectiveItem.[Key] [Key], LearnerGlobalObjectiveItem.ScaledScore ScaledScore, LearnerGlobalObjectiveItem.SuccessStatus SuccessStatus FROM GlobalObjectiveItem INNER JOIN LearnerGlobalObjectiveItem ON LearnerGlobalObjectiveItem.GlobalObjectiveId=GlobalObjectiveItem.Id ) GO GRANT SELECT ON [SeqNavLearnerGlobalObjectiveView] TO LearningStore GO -- Create a function that implements the SeqNavActivityPackageView view ALTER FUNCTION [SeqNavActivityPackageView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ActivityPackageItem.Id, ActivityPackageItem.PackageId,PackageItem.PackageFormat PackageFormat, PackageItem.Location PackagePath FROM ActivityPackageItem INNER JOIN PackageItem ON ActivityPackageItem.PackageId=PackageItem.Id ) GO GRANT SELECT ON [SeqNavActivityPackageView] TO LearningStore GO -- Create a function that implements the SeqNavActivityTreeView view ALTER FUNCTION [SeqNavActivityTreeView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( WITH TreeQuery(Id, ParentActivityId, DataModelCache, RootActivityId, ObjectivesGlobalToSystem) AS ( SELECT Id, ParentActivityId, DataModelCache, Id, ObjectivesGlobalToSystem FROM ActivityPackageItem UNION ALL SELECT t1.Id, t1.ParentActivityId, t1.DataModelCache, t2.RootActivityId, t1.ObjectivesGlobalToSystem FROM TreeQuery t2 INNER JOIN ActivityPackageItem t1 ON t1.ParentActivityId=t2.Id ) SELECT Id, ParentActivityId, DataModelCache, RootActivityId, ObjectivesGlobalToSystem FROM TreeQuery ) GO GRANT SELECT ON [SeqNavActivityTreeView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptView view ALTER FUNCTION [SeqNavAttemptView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT AttemptItem.Id Id, AttemptItem.AttemptStatus, AttemptItem.LogDetailSequencing, AttemptItem.LogFinalSequencing, AttemptItem.LogRollup, AttemptItem.CurrentActivityId, AttemptItem.SuspendedActivityId, AttemptItem.RootActivityId,AttemptItem.StartedTimestamp, AttemptItem.FinishedTimestamp,PackageItem.Id PackageId, PackageItem.PackageFormat PackageFormat, PackageItem.Location PackagePath, UserItem.Id LearnerId, UserItem.Name LearnerName, UserItem.AudioCaptioning LearnerAudioCaptioning, UserItem.AudioLevel LearnerAudioLevel, UserItem.DeliverySpeed LearnerDeliverySpeed, UserItem.Language LearnerLanguage, AttemptItem.TotalPoints, AttemptItem.SuccessStatus, AttemptItem.CompletionStatus FROM AttemptItem INNER JOIN PackageItem ON AttemptItem.PackageId=PackageItem.Id INNER JOIN UserItem ON AttemptItem.LearnerId=UserItem.Id ) GO GRANT SELECT ON [SeqNavAttemptView] TO LearningStore GO -- Create a function that implements the SeqNavActivityAttemptView view ALTER FUNCTION [SeqNavActivityAttemptView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ActivityAttemptItem.Id, ActivityAttemptItem.DataModelCache, ActivityAttemptItem.SequencingDataCache, ActivityAttemptItem.RandomPlacement, ActivityAttemptItem.AttemptId, ActivityAttemptItem.ActivityPackageId, ActivityPackageItem.DataModelCache StaticDataModelCache, ActivityPackageItem.ParentActivityId ParentId, ActivityPackageItem.ObjectivesGlobalToSystem ObjectivesGlobalToSystem, ActivityAttemptItem.CompletionStatus, ActivityAttemptItem.AttemptCount, ActivityAttemptItem.EvaluationPoints, ActivityAttemptItem.[Exit], ActivityAttemptItem.LessonStatus, ActivityAttemptItem.Location, ActivityAttemptItem.MinScore, ActivityAttemptItem.MaxScore, ActivityAttemptItem.ProgressMeasure, ActivityAttemptItem.RawScore, ActivityAttemptItem.ScaledScore, ActivityAttemptItem.SessionStartTimestamp, ActivityAttemptItem.SessionTime, ActivityAttemptItem.SuccessStatus, ActivityAttemptItem.SuspendData, ActivityAttemptItem.TotalTime FROM ActivityAttemptItem INNER JOIN ActivityPackageItem ON ActivityAttemptItem.ActivityPackageId=ActivityPackageItem.Id ) GO GRANT SELECT ON [SeqNavActivityAttemptView] TO LearningStore GO -- Create a function that implements the SeqNavCurrentActivityAttemptView view ALTER FUNCTION [SeqNavCurrentActivityAttemptView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ActivityAttemptItem.Id, ActivityAttemptItem.DataModelCache DataModelCache, ActivityAttemptItem.SequencingDataCache, ActivityAttemptItem.RandomPlacement, ActivityAttemptItem.AttemptId, ActivityPackageItem.DataModelCache StaticDataModelCache, ActivityPackageItem.ObjectivesGlobalToSystem ObjectivesGlobalToSystem, ActivityPackageItem.Credit Credit FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN ActivityPackageItem ON ActivityAttemptItem.ActivityPackageId=ActivityPackageItem.Id WHERE AttemptItem.CurrentActivityId=ActivityPackageItem.Id ) GO GRANT SELECT ON [SeqNavCurrentActivityAttemptView] TO LearningStore GO -- Create a function that implements the SeqNavCurrentCommentFromLmsView view ALTER FUNCTION [SeqNavCurrentCommentFromLmsView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT CommentFromLmsItem.Comment, CommentFromLmsItem.Location, CommentFromLmsItem.Timestamp, AttemptItem.Id AttemptId, ActivityAttemptItem.DataModelCache FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN ActivityPackageItem ON ActivityAttemptItem.ActivityPackageId=ActivityPackageItem.Id INNER JOIN CommentFromLmsItem ON ActivityPackageItem.Id=CommentFromLmsItem.ActivityPackageId WHERE AttemptItem.CurrentActivityId=ActivityPackageItem.Id ) GO GRANT SELECT ON [SeqNavCurrentCommentFromLmsView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptObjectiveView view ALTER FUNCTION [SeqNavAttemptObjectiveView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT AttemptObjectiveItem.Id AttemptObjectiveId, AttemptItem.Id AttemptId, AttemptObjectiveItem.CompletionStatus, AttemptObjectiveItem.Description, AttemptObjectiveItem.[Key], AttemptObjectiveItem.LessonStatus, AttemptObjectiveItem.RawScore, AttemptObjectiveItem.MinScore, AttemptObjectiveItem.MaxScore, AttemptObjectiveItem.ProgressMeasure, AttemptObjectiveItem.ScaledScore, AttemptObjectiveItem.SuccessStatus, AttemptObjectiveItem.ActivityAttemptId, AttemptObjectiveItem.IsPrimaryObjective FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN AttemptObjectiveItem ON AttemptObjectiveItem.ActivityAttemptId=ActivityAttemptItem.Id ) GO GRANT SELECT ON [SeqNavAttemptObjectiveView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptCommentFromLearnerView view ALTER FUNCTION [SeqNavAttemptCommentFromLearnerView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT CommentFromLearnerItem.Id CommentFromLearnerId, AttemptItem.Id AttemptId, CommentFromLearnerItem.Comment, CommentFromLearnerItem.Location, CommentFromLearnerItem.Timestamp, CommentFromLearnerItem.ActivityAttemptId, CommentFromLearnerItem.Ordinal FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN CommentFromLearnerItem ON CommentFromLearnerItem.ActivityAttemptId=ActivityAttemptItem.Id ) GO GRANT SELECT ON [SeqNavAttemptCommentFromLearnerView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptInteractionView view ALTER FUNCTION [SeqNavAttemptInteractionView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT InteractionItem.Id InteractionId, AttemptItem.Id AttemptId, InteractionItem.InteractionIdFromCmi, InteractionItem.InteractionType, InteractionItem.Timestamp, InteractionItem.Weighting, InteractionItem.ResultState, InteractionItem.ResultNumeric, InteractionItem.Latency, InteractionItem.Description, InteractionItem.LearnerResponseBool, InteractionItem.LearnerResponseString, InteractionItem.LearnerResponseNumeric, InteractionItem.ScaledScore, InteractionItem.RawScore, InteractionItem.MinScore, InteractionItem.MaxScore, InteractionItem.ActivityAttemptId, InteractionItem.EvaluationPoints FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN InteractionItem ON InteractionItem.ActivityAttemptId=ActivityAttemptItem.Id ) GO GRANT SELECT ON [SeqNavAttemptInteractionView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptEvaluationCommentLearnerView view ALTER FUNCTION [SeqNavAttemptEvaluationCommentLearnerView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT EvaluationCommentItem.Id EvaluationCommentId, AttemptItem.Id AttemptId, EvaluationCommentItem.Comment, EvaluationCommentItem.Location, EvaluationCommentItem.Timestamp, EvaluationCommentItem.InteractionId, EvaluationCommentItem.Ordinal FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN InteractionItem ON InteractionItem.ActivityAttemptId=ActivityAttemptItem.Id INNER JOIN EvaluationCommentItem ON EvaluationCommentItem.InteractionId=InteractionItem.Id ) GO GRANT SELECT ON [SeqNavAttemptEvaluationCommentLearnerView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptCorrectResponseView view ALTER FUNCTION [SeqNavAttemptCorrectResponseView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT CorrectResponseItem.Id CorrectResponseId, AttemptItem.Id AttemptId, CorrectResponseItem.InteractionId, CorrectResponseItem.ResponsePattern FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN InteractionItem ON InteractionItem.ActivityAttemptId=ActivityAttemptItem.Id INNER JOIN CorrectResponseItem ON CorrectResponseItem.InteractionId=InteractionItem.Id ) GO GRANT SELECT ON [SeqNavAttemptCorrectResponseView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptInteractionObjectiveView view ALTER FUNCTION [SeqNavAttemptInteractionObjectiveView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT InteractionObjectiveItem.Id InteractionObjectiveId, AttemptItem.Id AttemptId, InteractionObjectiveItem.AttemptObjectiveId, InteractionObjectiveItem.InteractionId FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN InteractionItem ON InteractionItem.ActivityAttemptId=ActivityAttemptItem.Id INNER JOIN InteractionObjectiveItem ON InteractionObjectiveItem.InteractionId=InteractionItem.Id ) GO GRANT SELECT ON [SeqNavAttemptInteractionObjectiveView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptExtensionDataView view ALTER FUNCTION [SeqNavAttemptExtensionDataView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ExtensionDataItem.Id ExtensionDataId, AttemptItem.Id AttemptId, ExtensionDataItem.Name, ExtensionDataItem.StringValue, ExtensionDataItem.IntValue, ExtensionDataItem.BoolValue, ExtensionDataItem.DoubleValue, ExtensionDataItem.DateTimeValue, ExtensionDataItem.AttachmentGuid, ExtensionDataItem.ActivityAttemptId FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN ExtensionDataItem ON ExtensionDataItem.ActivityAttemptId=ActivityAttemptItem.Id WHERE ExtensionDataItem.AttemptObjectiveId IS NULL AND ExtensionDataItem.InteractionId IS NULL ) GO GRANT SELECT ON [SeqNavAttemptExtensionDataView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptObjectiveExtensionDataView view ALTER FUNCTION [SeqNavAttemptObjectiveExtensionDataView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ExtensionDataItem.Id ExtensionDataId, ExtensionDataItem.AttemptObjectiveId, AttemptItem.Id AttemptId, ExtensionDataItem.Name, ExtensionDataItem.StringValue, ExtensionDataItem.IntValue, ExtensionDataItem.BoolValue, ExtensionDataItem.DoubleValue, ExtensionDataItem.DateTimeValue, ExtensionDataItem.AttachmentGuid, ExtensionDataItem.AttachmentValue FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN AttemptObjectiveItem ON AttemptObjectiveItem.ActivityAttemptId=ActivityAttemptItem.Id INNER JOIN ExtensionDataItem ON ExtensionDataItem.AttemptObjectiveId=AttemptObjectiveItem.Id WHERE ExtensionDataItem.InteractionId IS NULL AND ExtensionDataItem.AttemptObjectiveId IS NOT NULL ) GO GRANT SELECT ON [SeqNavAttemptObjectiveExtensionDataView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptInteractionExtensionDataView view ALTER FUNCTION [SeqNavAttemptInteractionExtensionDataView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ExtensionDataItem.Id ExtensionDataId, ExtensionDataItem.InteractionId, AttemptItem.Id AttemptId, ExtensionDataItem.Name, ExtensionDataItem.StringValue, ExtensionDataItem.IntValue, ExtensionDataItem.BoolValue, ExtensionDataItem.DoubleValue, ExtensionDataItem.DateTimeValue, ExtensionDataItem.AttachmentGuid, ExtensionDataItem.AttachmentValue FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN InteractionItem ON InteractionItem.ActivityAttemptId=ActivityAttemptItem.Id INNER JOIN ExtensionDataItem ON ExtensionDataItem.InteractionId=InteractionItem.Id WHERE ExtensionDataItem.AttemptObjectiveId IS NULL AND ExtensionDataItem.InteractionId IS NOT NULL ) GO GRANT SELECT ON [SeqNavAttemptInteractionExtensionDataView] TO LearningStore GO -- Create a function that implements the SeqNavAttemptRubricView view ALTER FUNCTION [SeqNavAttemptRubricView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT RubricItem.Id RubricItemId, AttemptItem.Id AttemptId, RubricItem.InteractionId, RubricItem.Ordinal, RubricItem.IsSatisfied, RubricItem.Points FROM AttemptItem INNER JOIN ActivityAttemptItem ON AttemptItem.Id=ActivityAttemptItem.AttemptId INNER JOIN InteractionItem ON InteractionItem.ActivityAttemptId=ActivityAttemptItem.Id INNER JOIN RubricItem ON RubricItem.InteractionId=InteractionItem.Id ) GO GRANT SELECT ON [SeqNavAttemptRubricView] TO LearningStore GO -- Create a function that implements the LearnerAssignmentView view ALTER FUNCTION [LearnerAssignmentView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ----- from LearnerAssignmentItem ----- lai.Id LearnerAssignmentId, lai.GuidId LearnerAssignmentGuidId, lai.LearnerId LearnerId, ----- from AssignmentItem ----- asi.AutoReturn AssignmentAutoReturn, asi.RootActivityId RootActivityId, ----- from AttemptItem ----- ati.Id AttemptId, ati.TotalPoints AttemptGradedPoints, ----- computed LearnerAssignmentState ----- dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) LearnerAssignmentState ----- FROM LearnerAssignmentItem lai INNER JOIN AssignmentItem asi ON lai.AssignmentId = asi.Id LEFT OUTER JOIN AttemptItem ati ON ati.LearnerAssignmentId = lai.Id ) GO GRANT SELECT ON [LearnerAssignmentView] TO LearningStore GO -- Create a function that implements the AssignmentPropertiesView view ALTER FUNCTION [AssignmentPropertiesView](@UserKey nvarchar(250),@AssignmentId bigint=NULL,@IsInstructor bit=NULL) RETURNS TABLE AS RETURN ( SELECT ----- from AssignmentItem ----- asi.Id AssignmentId, asi.SPSiteGuid AssignmentSPSiteGuid, asi.SPWebGuid AssignmentSPWebGuid, asi.NonELearningLocation AssignmentNonELearningLocation, asi.Title AssignmentTitle, asi.StartDate AssignmentStartDate, asi.DueDate AssignmentDueDate, asi.PointsPossible AssignmentPointsPossible, asi.Description AssignmentDescription, asi.AutoReturn AssignmentAutoReturn, asi.ShowAnswersToLearners AssignmentShowAnswersToLearners, asi.CreatedBy AssignmentCreatedById, cbui.[Name] AssignmentCreatedByName, cbui.[Key] AssignmentCreatedByKey, asi.DateCreated AssignmentDateCreated, asi.RootActivityId RootActivityId, ----- from PackageItem ----- pki.Id PackageId, pki.PackageFormat PackageFormat, pki.Location PackageLocation ----- FROM AssignmentItem asi INNER JOIN UserItem cbui ON cbui.Id = asi.CreatedBy LEFT OUTER JOIN ActivityPackageItem api ON asi.RootActivityId = api.Id LEFT OUTER JOIN PackageItem pki ON api.PackageId = pki.Id WHERE asi.Id = @AssignmentId ) GO GRANT SELECT ON [AssignmentPropertiesView] TO LearningStore GO -- ALTER FUNCTION for the security on the AssignmentPropertiesView view ALTER FUNCTION [AssignmentPropertiesView$Security](@UserKey nvarchar(250),@AssignmentId bigint=NULL,@IsInstructor bit=NULL) RETURNS bit AS BEGIN RETURN ( CASE WHEN EXISTS ( SELECT * FROM AssignmentItem asi LEFT OUTER JOIN LearnerAssignmentItem lai ON lai.AssignmentId = asi.Id LEFT OUTER JOIN InstructorAssignmentItem iai ON iai.AssignmentId = asi.Id LEFT OUTER JOIN UserItem lui ON lai.LearnerId = lui.Id LEFT OUTER JOIN UserItem iui ON iai.InstructorId = iui.Id WHERE asi.Id = @AssignmentId AND (((lui.[Key] = @UserKey) AND (@IsInstructor = 0) AND (GETUTCDATE() >= asi.StartDate)) OR ((iui.[Key]=@UserKey) AND (@IsInstructor = 1))) ) THEN 1 ELSE 0 END ) END GO GRANT EXECUTE ON [AssignmentPropertiesView$Security] TO LearningStore GO -- Create a function that implements the AssignmentListForInstructors view ALTER FUNCTION [AssignmentListForInstructors](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ----- from AssignmentItem ----- asi.Id AssignmentId, asi.SPSiteGuid AssignmentSPSiteGuid, asi.SPWebGuid AssignmentSPWebGuid, asi.NonELearningLocation AssignmentNonELearningLocation, asi.Title AssignmentTitle, asi.StartDate AssignmentStartDate, asi.DueDate AssignmentDueDate, asi.PointsPossible AssignmentPointsPossible, asi.Description AssignmentDescription, asi.AutoReturn AssignmentAutoReturn, asi.ShowAnswersToLearners AssignmentShowAnswersToLearners, asi.CreatedBy AssignmentCreatedById, cbui.[Name] AssignmentCreatedByName, cbui.[Key] AssignmentCreatedByKey, asi.DateCreated AssignmentDateCreated, asi.RootActivityId RootActivityId, ----- from PackageItem ----- pki.Id PackageId, pki.PackageFormat PackageFormat, pki.Location PackageLocation, ----- from learner assignment computed info ----- ISNULL(la.CountTotal,0) CountTotal, ISNULL(la.CountNotStarted,0) CountNotStarted, ISNULL(la.CountActive,0) CountActive, ISNULL(la.CountCompleted,0) CountCompleted, ISNULL(la.CountFinal,0) CountFinal, ISNULL(la.CountStarted,0) CountStarted, ISNULL(la.CountNotStartedOrActive,0) CountNotStartedOrActive, ISNULL(la.CountCompletedOrFinal,0) CountCompletedOrFinal, ISNULL(la.CountNotFinal,0) CountNotFinal, la.MinGradedPoints MinGradedPoints, la.MaxGradedPoints MaxGradedPoints, la.AvgGradedPoints AvgGradedPoints, la.MinFinalPoints MinFinalPoints, la.MaxFinalPoints MaxFinalPoints, la.AvgFinalPoints AvgFinalPoints ----- FROM AssignmentItem asi INNER JOIN UserItem cbui ON cbui.Id = asi.CreatedBy INNER JOIN InstructorAssignmentItem iai ON asi.Id = iai.AssignmentId INNER JOIN UserItem iui ON iai.InstructorId = iui.Id LEFT OUTER JOIN ActivityPackageItem api ON asi.RootActivityId = api.Id LEFT OUTER JOIN PackageItem pki ON api.PackageId = pki.Id LEFT OUTER JOIN ( SELECT ----- from Assignment ----- lai.AssignmentId, ----- computed CountTotal ----- COUNT(lai.Id) CountTotal, ----- computed CountNotStarted ----- COUNT(CASE WHEN dbo.IsLearnerAssignmentStarted(asi.RootActivityId, lai.NonELearningStatus, ati.AttemptStatus) = 0 THEN 1 ELSE NULL END) CountNotStarted, ----- computed CountActive ----- COUNT(CASE WHEN dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) = 1 THEN 1 ELSE NULL END) CountActive, ----- computed CountCompleted ----- COUNT(CASE WHEN dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) = 2 THEN 1 ELSE NULL END) CountCompleted, ----- computed CountFinal ----- COUNT(CASE WHEN lai.IsFinal = 1 THEN 1 ELSE NULL END) CountFinal, ----- computed CountStarted ----- COUNT(CASE WHEN dbo.IsLearnerAssignmentStarted(asi.RootActivityId, lai.NonELearningStatus, ati.AttemptStatus) <> 0 THEN 1 ELSE NULL END) CountStarted, ----- computed CountNotStartedOrActive ----- COUNT(CASE WHEN dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) IN (0,1) THEN 1 ELSE NULL END) CountNotStartedOrActive, ----- computed CountCompletedOrFinal ----- COUNT(CASE WHEN dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) IN (2,3) THEN 1 ELSE NULL END) CountCompletedOrFinal, ----- computed CountNotFinal ----- COUNT(CASE WHEN lai.IsFinal = 0 THEN 1 ELSE NULL END) CountNotFinal, ----- computed MinGradedPoints ----- MIN(ati.TotalPoints) MinGradedPoints, ----- computed MaxGradedPoints ----- MAX(ati.TotalPoints) MaxGradedPoints, ----- computed AvgGradedPoints ----- AVG(ati.TotalPoints) AvgGradedPoints, ----- computed MinFinalPoints ----- MIN(lai.FinalPoints) MinFinalPoints, ----- computed MaxFinalPoints ----- MAX(lai.FinalPoints) MaxFinalPoints, ----- computed AvgFinalPoints ----- AVG(lai.FinalPoints) AvgFinalPoints ----- FROM LearnerAssignmentItem lai INNER JOIN AssignmentItem asi ON lai.AssignmentId = asi.Id LEFT OUTER JOIN AttemptItem ati ON ati.LearnerAssignmentId = lai.Id INNER JOIN InstructorAssignmentItem iai ON asi.Id = iai.AssignmentId INNER JOIN UserItem iui ON iai.InstructorId = iui.Id WHERE iui.[Key] = @UserKey GROUP BY lai.AssignmentId) AS la ON asi.Id = la.AssignmentId WHERE iui.[Key] = @UserKey ) GO GRANT SELECT ON [AssignmentListForInstructors] TO LearningStore GO -- ALTER FUNCTION for the security on the AssignmentListForInstructors view ALTER FUNCTION [AssignmentListForInstructors$Security](@UserKey nvarchar(250)) RETURNS bit AS BEGIN RETURN (1) END GO GRANT EXECUTE ON [AssignmentListForInstructors$Security] TO LearningStore GO -- Create a function that implements the InstructorAssignmentListForInstructors view ALTER FUNCTION [InstructorAssignmentListForInstructors](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ----- from InstructorAssignmentItem ----- iai.Id InstructorAssignmentId, iai.InstructorId InstructorId, iui.[Name] InstructorName, iui.[Key] InstructorKey, ----- from AssignmentItem ----- asi.Id AssignmentId, asi.SPSiteGuid AssignmentSPSiteGuid, asi.SPWebGuid AssignmentSPWebGuid, asi.NonELearningLocation AssignmentNonELearningLocation, asi.Title AssignmentTitle, asi.StartDate AssignmentStartDate, asi.DueDate AssignmentDueDate, asi.PointsPossible AssignmentPointsPossible, asi.Description AssignmentDescription, asi.AutoReturn AssignmentAutoReturn, asi.ShowAnswersToLearners AssignmentShowAnswersToLearners, asi.CreatedBy AssignmentCreatedById, cbui.[Name] AssignmentCreatedByName, cbui.[Key] AssignmentCreatedByKey, ----- from PackageItem ----- pki.Id PackageId, pki.PackageFormat PackageFormat, pki.Location PackageLocation, pki.Manifest PackageManifest ----- FROM InstructorAssignmentItem iai INNER JOIN AssignmentItem asi ON iai.AssignmentId = asi.Id INNER JOIN UserItem iui ON iui.Id = iai.InstructorId INNER JOIN UserItem cbui ON cbui.Id = asi.CreatedBy LEFT OUTER JOIN ActivityPackageItem api ON asi.RootActivityId = api.Id LEFT OUTER JOIN PackageItem pki on api.PackageId = pki.Id WHERE EXISTS ( SELECT * FROM InstructorAssignmentItem iai2 INNER JOIN UserItem ui2 ON iai2.InstructorId = ui2.Id WHERE iai2.AssignmentId = asi.Id AND ui2.[Key] = @UserKey ) ) GO GRANT SELECT ON [InstructorAssignmentListForInstructors] TO LearningStore GO -- ALTER FUNCTION for the security on the InstructorAssignmentListForInstructors view ALTER FUNCTION [InstructorAssignmentListForInstructors$Security](@UserKey nvarchar(250)) RETURNS bit AS BEGIN RETURN (1) END GO GRANT EXECUTE ON [InstructorAssignmentListForInstructors$Security] TO LearningStore GO -- Create a function that implements the LearnerAssignmentListForLearners view ALTER FUNCTION [LearnerAssignmentListForLearners](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ----- from LearnerAssignmentItem ----- lai.Id LearnerAssignmentId, lai.GuidId LearnerAssignmentGuidId, lai.LearnerId LearnerId, lui.[Name] LearnerName, lui.[Key] LearnerKey, lai.IsFinal IsFinal, lai.NonELearningStatus NonELearningStatus, CASE WHEN lai.IsFinal = 1 THEN lai.FinalPoints ELSE NULL END FinalPoints, lai.InstructorComments InstructorComments, ----- from AssignmentItem ----- asi.Id AssignmentId, asi.SPSiteGuid AssignmentSPSiteGuid, asi.SPWebGuid AssignmentSPWebGuid, asi.NonELearningLocation AssignmentNonELearningLocation, asi.Title AssignmentTitle, asi.StartDate AssignmentStartDate, asi.DueDate AssignmentDueDate, asi.PointsPossible AssignmentPointsPossible, asi.Description AssignmentDescription, asi.AutoReturn AssignmentAutoReturn, asi.ShowAnswersToLearners AssignmentShowAnswersToLearners, asi.CreatedBy AssignmentCreatedById, cbui.[Name] AssignmentCreatedByName, cbui.[Key] AssignmentCreatedByKey, asi.DateCreated AssignmentDateCreated, asi.RootActivityId RootActivityId, ----- from PackageItem ----- pki.Id PackageId, pki.PackageFormat PackageFormat, pki.Location PackageLocation, ----- from AttemptItem ----- ati.Id AttemptId, ati.CurrentActivityId AttemptCurrentActivityId, ati.SuspendedActivityId AttemptSuspendedActivityId, ati.AttemptStatus AttemptStatus, ati.FinishedTimestamp AttemptFinishedTimestamp, ati.LogDetailSequencing AttemptLogDetailSequencing, ati.LogFinalSequencing AttemptLogFinalSequencing, ati.LogRollup AttemptLogRollup, ati.StartedTimestamp AttemptStartedTimestamp, ati.CompletionStatus AttemptCompletionStatus, ati.SuccessStatus AttemptSuccessStatus, ati.TotalPoints AttemptGradedPoints, ----- computed LearnerAssignmentState ----- dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) LearnerAssignmentState, ----- computed HasInstructors ----- CASE WHEN EXISTS ( SELECT * FROM InstructorAssignmentItem iaiH WHERE iaiH.AssignmentId = asi.Id ) THEN 1 ELSE 0 END HasInstructors ----- FROM LearnerAssignmentItem lai INNER JOIN AssignmentItem asi ON lai.AssignmentId = asi.Id INNER JOIN UserItem lui ON lui.Id = lai.LearnerId INNER JOIN UserItem cbui ON cbui.Id = asi.CreatedBy LEFT OUTER JOIN ActivityPackageItem api ON asi.RootActivityId = api.Id LEFT OUTER JOIN PackageItem pki on api.PackageId = pki.Id LEFT OUTER JOIN AttemptItem ati ON ati.LearnerAssignmentId = lai.Id WHERE lui.[Key] = @UserKey AND GETUTCDATE() >= asi.StartDate ) GO GRANT SELECT ON [LearnerAssignmentListForLearners] TO LearningStore GO -- ALTER FUNCTION for the security on the LearnerAssignmentListForLearners view ALTER FUNCTION [LearnerAssignmentListForLearners$Security](@UserKey nvarchar(250)) RETURNS bit AS BEGIN RETURN (1) END GO GRANT EXECUTE ON [LearnerAssignmentListForLearners$Security] TO LearningStore GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LearnerAssignmentListForObservers]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[LearnerAssignmentListForObservers] GO -- Create a function that implements the LearnerAssignmentListForObservers view CREATE FUNCTION [LearnerAssignmentListForObservers](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ----- from LearnerAssignmentItem ----- lai.Id LearnerAssignmentId, lai.GuidId LearnerAssignmentGuidId, lai.LearnerId LearnerId, lui.[Name] LearnerName, lui.[Key] LearnerKey, lai.IsFinal IsFinal, lai.NonELearningStatus NonELearningStatus, CASE WHEN lai.IsFinal = 1 THEN lai.FinalPoints ELSE NULL END FinalPoints, lai.InstructorComments InstructorComments, ----- from AssignmentItem ----- asi.Id AssignmentId, asi.SPSiteGuid AssignmentSPSiteGuid, asi.SPWebGuid AssignmentSPWebGuid, asi.NonELearningLocation AssignmentNonELearningLocation, asi.Title AssignmentTitle, asi.StartDate AssignmentStartDate, asi.DueDate AssignmentDueDate, asi.PointsPossible AssignmentPointsPossible, asi.Description AssignmentDescription, asi.AutoReturn AssignmentAutoReturn, asi.ShowAnswersToLearners AssignmentShowAnswersToLearners, asi.CreatedBy AssignmentCreatedById, cbui.[Name] AssignmentCreatedByName, cbui.[Key] AssignmentCreatedByKey, asi.DateCreated AssignmentDateCreated, asi.RootActivityId RootActivityId, ----- from PackageItem ----- pki.Id PackageId, pki.PackageFormat PackageFormat, pki.Location PackageLocation, ----- from learner assignment computed info ----- ISNULL(la.CountTotal,0) CountTotal, ISNULL(la.CountNotStarted,0) CountNotStarted, ISNULL(la.CountActive,0) CountActive, ISNULL(la.CountCompleted,0) CountCompleted, ISNULL(la.CountFinal,0) CountFinal, ISNULL(la.CountStarted,0) CountStarted, ISNULL(la.CountNotStartedOrActive,0) CountNotStartedOrActive, ISNULL(la.CountCompletedOrFinal,0) CountCompletedOrFinal, ISNULL(la.CountNotFinal,0) CountNotFinal, la.MinGradedPoints MinGradedPoints, la.MaxGradedPoints MaxGradedPoints, la.AvgGradedPoints AvgGradedPoints, la.MinFinalPoints MinFinalPoints, la.MaxFinalPoints MaxFinalPoints, la.AvgFinalPoints AvgFinalPoints, ----- from AttemptItem ----- ati.Id AttemptId, ati.CurrentActivityId AttemptCurrentActivityId, ati.SuspendedActivityId AttemptSuspendedActivityId, ati.AttemptStatus AttemptStatus, ati.FinishedTimestamp AttemptFinishedTimestamp, ati.LogDetailSequencing AttemptLogDetailSequencing, ati.LogFinalSequencing AttemptLogFinalSequencing, ati.LogRollup AttemptLogRollup, ati.StartedTimestamp AttemptStartedTimestamp, ati.CompletionStatus AttemptCompletionStatus, ati.SuccessStatus AttemptSuccessStatus, ati.TotalPoints AttemptGradedPoints, ----- computed LearnerAssignmentState ----- dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) LearnerAssignmentState, ----- computed HasInstructors ----- CASE WHEN EXISTS ( SELECT * FROM InstructorAssignmentItem iaiH WHERE iaiH.AssignmentId = asi.Id ) THEN 1 ELSE 0 END HasInstructors ------------------------------------ FROM AssignmentItem asi INNER JOIN UserItem cbui ON cbui.Id = asi.CreatedBy INNER JOIN LearnerAssignmentItem lai ON asi.Id = lai.AssignmentId INNER JOIN UserItem lui ON lai.LearnerId = lui.Id LEFT OUTER JOIN ActivityPackageItem api ON asi.RootActivityId = api.Id LEFT OUTER JOIN PackageItem pki ON api.PackageId = pki.Id LEFT OUTER JOIN AttemptItem ati ON ati.LearnerAssignmentId = lai.Id INNER JOIN ( SELECT ----- from Assignment ----- lai.AssignmentId, ----- computed CountTotal ----- COUNT(lai.Id) CountTotal, ----- computed CountNotStarted ----- COUNT(CASE WHEN dbo.IsLearnerAssignmentStarted(asi.RootActivityId, lai.NonELearningStatus, ati.AttemptStatus) = 0 THEN 1 ELSE NULL END) CountNotStarted, ----- computed CountActive ----- COUNT(CASE WHEN dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) = 1 THEN 1 ELSE NULL END) CountActive, ----- computed CountCompleted ----- COUNT(CASE WHEN dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) = 2 THEN 1 ELSE NULL END) CountCompleted, ----- computed CountFinal ----- COUNT(CASE WHEN lai.IsFinal = 1 THEN 1 ELSE NULL END) CountFinal, ----- computed CountStarted ----- COUNT(CASE WHEN dbo.IsLearnerAssignmentStarted(asi.RootActivityId, lai.NonELearningStatus, ati.AttemptStatus) <> 0 THEN 1 ELSE NULL END) CountStarted, ----- computed CountNotStartedOrActive ----- COUNT(CASE WHEN dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) IN (0,1) THEN 1 ELSE NULL END) CountNotStartedOrActive, ----- computed CountCompletedOrFinal ----- COUNT(CASE WHEN dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) IN (2,3) THEN 1 ELSE NULL END) CountCompletedOrFinal, ----- computed CountNotFinal ----- COUNT(CASE WHEN lai.IsFinal = 0 THEN 1 ELSE NULL END) CountNotFinal, ----- computed MinGradedPoints ----- MIN(ati.TotalPoints) MinGradedPoints, ----- computed MaxGradedPoints ----- MAX(ati.TotalPoints) MaxGradedPoints, ----- computed AvgGradedPoints ----- AVG(ati.TotalPoints) AvgGradedPoints, ----- computed MinFinalPoints ----- MIN(lai.FinalPoints) MinFinalPoints, ----- computed MaxFinalPoints ----- MAX(lai.FinalPoints) MaxFinalPoints, ----- computed AvgFinalPoints ----- AVG(lai.FinalPoints) AvgFinalPoints ----- FROM LearnerAssignmentItem lai INNER JOIN AssignmentItem asi ON lai.AssignmentId = asi.Id LEFT OUTER JOIN AttemptItem ati ON ati.LearnerAssignmentId = lai.Id INNER JOIN LearnerAssignmentItem iai ON asi.Id = iai.AssignmentId INNER JOIN UserItem iui ON iai.LearnerId = iui.Id GROUP BY lai.AssignmentId) AS la ON asi.Id = la.AssignmentId WHERE lui.[Key] = @UserKey ) GO GRANT SELECT ON [LearnerAssignmentListForObservers] TO LearningStore GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LearnerAssignmentListForObservers$Security]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[LearnerAssignmentListForObservers$Security] GO -- ALTER FUNCTION for the security on the LearnerAssignmentListForObservers view CREATE FUNCTION [LearnerAssignmentListForObservers$Security](@UserKey nvarchar(250)) RETURNS bit AS BEGIN RETURN (1) END GO GRANT EXECUTE ON [LearnerAssignmentListForObservers$Security] TO LearningStore GO -- Create a function that implements the LearnerAssignmentListForInstructors view ALTER FUNCTION [LearnerAssignmentListForInstructors](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ----- from LearnerAssignmentItem ----- lai.Id LearnerAssignmentId, lai.GuidId LearnerAssignmentGuidId, lai.LearnerId LearnerId, lui.[Name] LearnerName, lui.[Key] LearnerKey, lai.IsFinal IsFinal, lai.NonELearningStatus NonELearningStatus, lai.FinalPoints FinalPoints, lai.InstructorComments InstructorComments, ----- from AssignmentItem ----- asi.Id AssignmentId, asi.SPSiteGuid AssignmentSPSiteGuid, asi.SPWebGuid AssignmentSPWebGuid, asi.NonELearningLocation AssignmentNonELearningLocation, asi.Title AssignmentTitle, asi.StartDate AssignmentStartDate, asi.DueDate AssignmentDueDate, asi.PointsPossible AssignmentPointsPossible, asi.Description AssignmentDescription, asi.AutoReturn AssignmentAutoReturn, asi.ShowAnswersToLearners AssignmentShowAnswersToLearners, asi.CreatedBy AssignmentCreatedById, cbui.[Name] AssignmentCreatedByName, cbui.[Key] AssignmentCreatedByKey, asi.RootActivityId RootActivityId, ----- from PackageItem ----- pki.Id PackageId, pki.PackageFormat PackageFormat, pki.Location PackageLocation, pki.Manifest PackageManifest, ----- from AttemptItem ----- ati.Id AttemptId, ati.CurrentActivityId AttemptCurrentActivityId, ati.SuspendedActivityId AttemptSuspendedActivityId, ati.AttemptStatus AttemptStatus, ati.FinishedTimestamp AttemptFinishedTimestamp, ati.LogDetailSequencing AttemptLogDetailSequencing, ati.LogFinalSequencing AttemptLogFinalSequencing, ati.LogRollup AttemptLogRollup, ati.StartedTimestamp AttemptStartedTimestamp, ati.CompletionStatus AttemptCompletionStatus, ati.SuccessStatus AttemptSuccessStatus, ati.TotalPoints AttemptGradedPoints, ----- computed LearnerAssignmentState ----- dbo.GetLearnerAssignmentState(asi.RootActivityId, lai.IsFinal, lai.NonELearningStatus, ati.AttemptStatus) LearnerAssignmentState, ----- computed HasInstructors ----- CASE WHEN EXISTS ( SELECT * FROM InstructorAssignmentItem iaiH WHERE iaiH.AssignmentId = asi.Id ) THEN 1 ELSE 0 END HasInstructors ----- FROM LearnerAssignmentItem lai INNER JOIN AssignmentItem asi ON lai.AssignmentId = asi.Id INNER JOIN UserItem lui ON lui.Id = lai.LearnerId INNER JOIN UserItem cbui ON cbui.Id = asi.CreatedBy INNER JOIN InstructorAssignmentItem iai ON lai.AssignmentId = iai.AssignmentId INNER JOIN UserItem iui ON iai.InstructorId = iui.Id LEFT OUTER JOIN ActivityPackageItem api ON asi.RootActivityId = api.Id LEFT OUTER JOIN PackageItem pki ON api.PackageId = pki.Id LEFT OUTER JOIN AttemptItem ati ON ati.LearnerAssignmentId = lai.Id WHERE iui.[Key] = @UserKey ) GO GRANT SELECT ON [LearnerAssignmentListForInstructors] TO LearningStore GO -- ALTER FUNCTION for the security on the LearnerAssignmentListForInstructors view ALTER FUNCTION [LearnerAssignmentListForInstructors$Security](@UserKey nvarchar(250)) RETURNS bit AS BEGIN RETURN (1) END GO GRANT EXECUTE ON [LearnerAssignmentListForInstructors$Security] TO LearningStore GO -- Create a function that implements the UserWebList view ALTER FUNCTION [UserWebList](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT SPSiteGuid, SPWebGuid, LastAccessTime FROM UserWebListItem WHERE OwnerKey = @UserKey ) GO GRANT SELECT ON [UserWebList] TO LearningStore GO -- ALTER FUNCTION for the security on the UserWebList view ALTER FUNCTION [UserWebList$Security](@UserKey nvarchar(250)) RETURNS bit AS BEGIN RETURN (1) END GO GRANT EXECUTE ON [UserWebList$Security] TO LearningStore GO -- Create a function that implements the ActivityPackageItemView view ALTER FUNCTION [ActivityPackageItemView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT ----- from PackageItem ----- pki.Id PackageId, pki.PackageFormat PackageFormat, pki.Location PackageLocation, pki.Manifest PackageManifest, ----- from ActivityPackageItem ----- api.Id Id, api.ActivityIdFromManifest ActivityIdFromManifest, api.OriginalPlacement OriginalPlacement, api.ParentActivityId ParentActivityId, api.PrimaryObjectiveId PrimaryObjectiveId, api.ResourceId ResourceId, api.PrimaryResourceFromManifest PrimaryResourceIdFromManifest, api.CompletionThreshold CompletionThreshold, api.Credit Credit, api.IsVisibleInContents IsVisibleInContents, api.LaunchData LaunchData, api.MaxAttempts MaxAttempts, api.MaxTimeAllowed MaxTimeAllowed, api.ResourceParameters ResourceParameters, api.ScaledPassingScore ScaledPassingScore, api.TimeLimitAction TimeLimitAction, api.Title Title, api.ObjectivesGlobalToSystem ObjectivesGlobalToSystem ----- FROM ActivityPackageItem api INNER JOIN PackageItem pki on api.PackageId = pki.Id ) GO GRANT SELECT ON [ActivityPackageItemView] TO LearningStore GO -- ALTER FUNCTION for the security on the ExecuteSessionRight right ALTER FUNCTION [ExecuteSessionRight](@UserKey nvarchar(250),@AttemptId bigint=NULL) RETURNS bit AS BEGIN RETURN ( CASE WHEN EXISTS ( SELECT * FROM AttemptItem ati LEFT OUTER JOIN LearnerAssignmentItem lai ON ati.LearnerAssignmentId = lai.Id LEFT OUTER JOIN AssignmentItem ai ON lai.AssignmentId = ai.Id LEFT OUTER JOIN InstructorAssignmentItem iai ON iai.AssignmentId = ai.Id INNER JOIN UserItem lui ON ati.LearnerId = lui.Id LEFT OUTER JOIN UserItem iui ON iai.InstructorId = iui.Id WHERE ati.Id = @AttemptId AND (lui.[Key] = @UserKey OR iui.[Key]=@UserKey) ) THEN 1 ELSE 0 END ) END GO GRANT EXECUTE ON [ExecuteSessionRight] TO LearningStore GO -- ALTER FUNCTION for the security on the ReviewSessionRight right ALTER FUNCTION [ReviewSessionRight](@UserKey nvarchar(250),@AttemptId bigint=NULL) RETURNS bit AS BEGIN RETURN ( 1 ) END GO GRANT EXECUTE ON [ReviewSessionRight] TO LearningStore GO -- ALTER FUNCTION for the security on the RandomAccessSessionRight right ALTER FUNCTION [RandomAccessSessionRight](@UserKey nvarchar(250),@AttemptId bigint=NULL) RETURNS bit AS BEGIN RETURN ( CASE WHEN EXISTS ( SELECT * FROM AttemptItem ati LEFT OUTER JOIN LearnerAssignmentItem lai ON ati.LearnerAssignmentId = lai.Id LEFT OUTER JOIN AssignmentItem ai ON lai.AssignmentId = ai.Id LEFT OUTER JOIN InstructorAssignmentItem iai ON iai.AssignmentId = ai.Id LEFT OUTER JOIN UserItem iui ON iai.InstructorId = iui.Id WHERE ati.Id = @AttemptId AND (ati.AttemptStatus = 1 OR ati.AttemptStatus = 2) AND iui.[Key]=@UserKey ) THEN 1 ELSE 0 END ) END GO GRANT EXECUTE ON [RandomAccessSessionRight] TO LearningStore GO -- ALTER FUNCTION for the security on the StartAttemptOnLearnerAssignmentRight right ALTER FUNCTION [StartAttemptOnLearnerAssignmentRight](@UserKey nvarchar(250),@LearnerAssignmentGuidId uniqueidentifier=NULL) RETURNS bit AS BEGIN RETURN ( CASE WHEN EXISTS ( SELECT * FROM LearnerAssignmentItem lai INNER JOIN AssignmentItem ai ON lai.AssignmentId = ai.Id LEFT OUTER JOIN AttemptItem ati ON lai.Id = ati.LearnerAssignmentId LEFT OUTER JOIN InstructorAssignmentItem iai ON iai.AssignmentId = lai.AssignmentId INNER JOIN UserItem lui ON lai.LearnerId = lui.Id LEFT OUTER JOIN UserItem iui ON iai.InstructorId = iui.Id WHERE lai.GuidId = @LearnerAssignmentGuidId AND ati.Id IS NULL AND ai.RootActivityId IS NOT NULL AND (lui.[Key]=@UserKey OR iui.[Key]=@UserKey) ) THEN 1 ELSE 0 END ) END GO GRANT EXECUTE ON [StartAttemptOnLearnerAssignmentRight] TO LearningStore GO -- ALTER FUNCTION for the security on the FinishLearnerAssignmentRight right ALTER FUNCTION [FinishLearnerAssignmentRight](@UserKey nvarchar(250),@LearnerAssignmentGuidId uniqueidentifier=NULL) RETURNS bit AS BEGIN RETURN ( CASE WHEN EXISTS ( SELECT * FROM LearnerAssignmentItem lai LEFT OUTER JOIN InstructorAssignmentItem iai ON iai.AssignmentId = lai.AssignmentId INNER JOIN UserItem lui ON lai.LearnerId = lui.Id LEFT OUTER JOIN UserItem iui ON iai.InstructorId = iui.Id WHERE lai.GuidId = @LearnerAssignmentGuidId AND (lui.[Key]=@UserKey OR iui.[Key]=@UserKey) ) THEN 1 ELSE 0 END ) END GO GRANT EXECUTE ON [FinishLearnerAssignmentRight] TO LearningStore GO -- ALTER FUNCTION for the security on the CompleteLearnerAssignmentRight right ALTER FUNCTION [CompleteLearnerAssignmentRight](@UserKey nvarchar(250),@LearnerAssignmentGuidId uniqueidentifier=NULL) RETURNS bit AS BEGIN RETURN ( CASE WHEN EXISTS ( SELECT * FROM LearnerAssignmentItem lai LEFT OUTER JOIN InstructorAssignmentItem iai ON iai.AssignmentId = lai.AssignmentId INNER JOIN UserItem lui ON lai.LearnerId = lui.Id LEFT OUTER JOIN UserItem iui ON iai.InstructorId = iui.Id WHERE lai.GuidId = @LearnerAssignmentGuidId AND (lui.[Key]=@UserKey OR iui.[Key]=@UserKey) ) THEN 1 ELSE 0 END ) END GO GRANT EXECUTE ON [CompleteLearnerAssignmentRight] TO LearningStore GO -- ALTER FUNCTION for the security on the FinalizeLearnerAssignmentRight right ALTER FUNCTION [FinalizeLearnerAssignmentRight](@UserKey nvarchar(250),@LearnerAssignmentGuidId uniqueidentifier=NULL) RETURNS bit AS BEGIN RETURN ( CASE WHEN EXISTS ( SELECT * FROM LearnerAssignmentItem lai INNER JOIN InstructorAssignmentItem iai ON iai.AssignmentId = lai.AssignmentId INNER JOIN UserItem iui ON iai.InstructorId = iui.Id WHERE lai.GuidId = @LearnerAssignmentGuidId AND iui.[Key]=@UserKey ) THEN 1 ELSE 0 END ) END GO GRANT EXECUTE ON [FinalizeLearnerAssignmentRight] TO LearningStore GO -- ALTER FUNCTION for the security on the ActivateLearnerAssignmentRight right ALTER FUNCTION [ActivateLearnerAssignmentRight](@UserKey nvarchar(250),@LearnerAssignmentGuidId uniqueidentifier=NULL) RETURNS bit AS BEGIN RETURN ( CASE WHEN EXISTS ( SELECT * FROM LearnerAssignmentItem lai INNER JOIN InstructorAssignmentItem iai ON iai.AssignmentId = lai.AssignmentId INNER JOIN UserItem iui ON iai.InstructorId = iui.Id WHERE lai.GuidId = @LearnerAssignmentGuidId AND iui.[Key]=@UserKey ) THEN 1 ELSE 0 END ) | ( CASE WHEN EXISTS ( SELECT * FROM LearnerAssignmentItem lai INNER JOIN UserItem lui ON lai.LearnerId = lui.Id INNER JOIN AssignmentItem ai ON lai.AssignmentId = ai.Id WHERE lai.GuidId = @LearnerAssignmentGuidId AND lui.[Key]=@UserKey AND ai.RootActivityId IS NULL AND (lai.NonELearningStatus IS NULL OR lai.NonELearningStatus = 0) ) THEN 1 ELSE 0 END ) END GO GRANT EXECUTE ON [ActivateLearnerAssignmentRight] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the ActivityAttemptItem item type ALTER FUNCTION [ActivityAttemptItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [AttemptId], [ActivityPackageId], [CompletionStatus], [AttemptCount], [DataModelCache], [EvaluationPoints], [Exit], [LessonStatus], [Location], [MinScore], [MaxScore], [ProgressMeasure], [RandomPlacement], [RawScore], [ScaledScore], [SequencingDataCache], [SessionStartTimestamp], [SessionTime], [SuccessStatus], [SuspendData], [TotalTime] FROM [ActivityAttemptItem] ) GO GRANT SELECT ON [ActivityAttemptItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the ActivityObjectiveItem item type ALTER FUNCTION [ActivityObjectiveItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [ActivityPackageId], [IsPrimaryObjective], [Key], [MinNormalizedMeasure], [SatisfiedByMeasure] FROM [ActivityObjectiveItem] ) GO GRANT SELECT ON [ActivityObjectiveItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the ActivityPackageItem item type ALTER FUNCTION [ActivityPackageItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [ActivityIdFromManifest], [OriginalPlacement], [ParentActivityId], [PackageId], [PrimaryObjectiveId], [ResourceId], [PrimaryResourceFromManifest], [DataModelCache], [CompletionThreshold], [Credit], [HideContinue], [HidePrevious], [HideExit], [HideAbandon], [IsVisibleInContents], [LaunchData], [MasteryScore], [MaxAttempts], [MaxTimeAllowed], [ResourceParameters], [ScaledPassingScore], [TimeLimitAction], [Title], [ObjectivesGlobalToSystem] FROM [ActivityPackageItem] ) GO GRANT SELECT ON [ActivityPackageItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the PackageGlobalObjectiveItem item type ALTER FUNCTION [PackageGlobalObjectiveItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [LearnerId], [GlobalObjectiveId], [ScaledScore], [SuccessStatus] FROM [PackageGlobalObjectiveItem] ) GO GRANT SELECT ON [PackageGlobalObjectiveItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the AttemptItem item type ALTER FUNCTION [AttemptItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [LearnerId], [RootActivityId], [CompletionStatus], [CurrentActivityId], [SuspendedActivityId], [PackageId], [AttemptStatus], [FinishedTimestamp], [LogDetailSequencing], [LogFinalSequencing], [LogRollup], [StartedTimestamp], [SuccessStatus], [TotalPoints], [LearnerAssignmentId] FROM [AttemptItem] ) GO GRANT SELECT ON [AttemptItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the AttemptObjectiveItem item type ALTER FUNCTION [AttemptObjectiveItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [ActivityAttemptId], [ActivityObjectiveId], [CompletionStatus], [Description], [IsPrimaryObjective], [Key], [LessonStatus], [RawScore], [MinScore], [MaxScore], [ProgressMeasure], [ScaledScore], [SuccessStatus] FROM [AttemptObjectiveItem] ) GO GRANT SELECT ON [AttemptObjectiveItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the CommentFromLearnerItem item type ALTER FUNCTION [CommentFromLearnerItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [ActivityAttemptId], [Comment], [Location], [Ordinal], [Timestamp] FROM [CommentFromLearnerItem] ) GO GRANT SELECT ON [CommentFromLearnerItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the CommentFromLmsItem item type ALTER FUNCTION [CommentFromLmsItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [ActivityPackageId], [Comment], [Location], [Timestamp] FROM [CommentFromLmsItem] ) GO GRANT SELECT ON [CommentFromLmsItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the CorrectResponseItem item type ALTER FUNCTION [CorrectResponseItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [InteractionId], [ResponsePattern] FROM [CorrectResponseItem] ) GO GRANT SELECT ON [CorrectResponseItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the EvaluationCommentItem item type ALTER FUNCTION [EvaluationCommentItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [InteractionId], [Comment], [Location], [Ordinal], [Timestamp] FROM [EvaluationCommentItem] ) GO GRANT SELECT ON [EvaluationCommentItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the ExtensionDataItem item type ALTER FUNCTION [ExtensionDataItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [ActivityAttemptId], [InteractionId], [AttemptObjectiveId], [Name], [AttachmentGuid], [AttachmentValue], [BoolValue], [DateTimeValue], [DoubleValue], [IntValue], [StringValue] FROM [ExtensionDataItem] ) GO GRANT SELECT ON [ExtensionDataItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the GlobalObjectiveItem item type ALTER FUNCTION [GlobalObjectiveItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [OrganizationId], [Key], [Description] FROM [GlobalObjectiveItem] ) GO GRANT SELECT ON [GlobalObjectiveItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the InteractionItem item type ALTER FUNCTION [InteractionItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [ActivityAttemptId], [InteractionIdFromCmi], [InteractionType], [Timestamp], [Weighting], [ResultState], [ResultNumeric], [Latency], [Description], [LearnerResponseBool], [LearnerResponseString], [LearnerResponseNumeric], [ScaledScore], [RawScore], [MinScore], [MaxScore], [EvaluationPoints] FROM [InteractionItem] ) GO GRANT SELECT ON [InteractionItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the InteractionObjectiveItem item type ALTER FUNCTION [InteractionObjectiveItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [InteractionId], [AttemptObjectiveId] FROM [InteractionObjectiveItem] ) GO GRANT SELECT ON [InteractionObjectiveItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the LearnerGlobalObjectiveItem item type ALTER FUNCTION [LearnerGlobalObjectiveItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [LearnerId], [GlobalObjectiveId], [ScaledScore], [SuccessStatus] FROM [LearnerGlobalObjectiveItem] ) GO GRANT SELECT ON [LearnerGlobalObjectiveItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the MapActivityObjectiveToGlobalObjectiveItem item type ALTER FUNCTION [MapActivityObjectiveToGlobalObjectiveItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [ActivityObjectiveId], [GlobalObjectiveId], [ReadSatisfiedStatus], [ReadNormalizedMeasure], [WriteSatisfiedStatus], [WriteNormalizedMeasure] FROM [MapActivityObjectiveToGlobalObjectiveItem] ) GO GRANT SELECT ON [MapActivityObjectiveToGlobalObjectiveItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the PackageItem item type ALTER FUNCTION [PackageItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [PackageFormat], [Location], [Manifest], [Warnings] FROM [PackageItem] ) GO GRANT SELECT ON [PackageItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the RubricItem item type ALTER FUNCTION [RubricItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [InteractionId], [Ordinal], [IsSatisfied], [Points] FROM [RubricItem] ) GO GRANT SELECT ON [RubricItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the ResourceItem item type ALTER FUNCTION [ResourceItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [PackageId], [ResourceXml] FROM [ResourceItem] ) GO GRANT SELECT ON [ResourceItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the SequencingLogEntryItem item type ALTER FUNCTION [SequencingLogEntryItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [AttemptId], [ActivityAttemptId], [EventType], [Message], [NavigationCommand], [Timestamp] FROM [SequencingLogEntryItem] ) GO GRANT SELECT ON [SequencingLogEntryItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the UserItem item type ALTER FUNCTION [UserItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [Key], [Name], [AudioCaptioning], [AudioLevel], [DeliverySpeed], [Language] FROM [UserItem] ) GO GRANT SELECT ON [UserItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the SiteSettingsItem item type ALTER FUNCTION [SiteSettingsItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [SiteGuid], [SettingsXml], [SettingsXmlLastModified] FROM [SiteSettingsItem] ) GO GRANT SELECT ON [SiteSettingsItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the AssignmentItem item type ALTER FUNCTION [AssignmentItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [SPSiteGuid], [SPWebGuid], [RootActivityId], [NonELearningLocation], [Title], [StartDate], [DueDate], [PointsPossible], [Description], [AutoReturn], [ShowAnswersToLearners], [CreatedBy], [DateCreated] FROM [AssignmentItem] ) GO GRANT SELECT ON [AssignmentItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the InstructorAssignmentItem item type ALTER FUNCTION [InstructorAssignmentItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [AssignmentId], [InstructorId] FROM [InstructorAssignmentItem] ) GO GRANT SELECT ON [InstructorAssignmentItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the LearnerAssignmentItem item type ALTER FUNCTION [LearnerAssignmentItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [GuidId], [AssignmentId], [LearnerId], [IsFinal], [NonELearningStatus], [FinalPoints], [InstructorComments] FROM [LearnerAssignmentItem] ) GO GRANT SELECT ON [LearnerAssignmentItem$DefaultView] TO LearningStore GO -- ALTER FUNCTION that implements the default view for the UserWebListItem item type ALTER FUNCTION [UserWebListItem$DefaultView](@UserKey nvarchar(250)) RETURNS TABLE AS RETURN ( SELECT Id, [OwnerKey], [SPSiteGuid], [SPWebGuid], [LastAccessTime] FROM [UserWebListItem] ) GO GRANT SELECT ON [UserWebListItem$DefaultView] TO LearningStore GO