MS CRM - Create Query to have an unsecured FilteredEmail view called FilteredEmailUS

CreateUnsecureFilteredEmailView.sql

create view [dbo].[FilteredEmailUS] (
    activityid,
    actualdurationminutes,
    actualend,
    actualendutc,
    actualstart,
    actualstartutc,
    category,
    compressed,
    compressedname,
    createdby,
    createdbydsc,
    createdbyname,
    createdbyyominame,
    createdon,
    createdonutc,
    deliveryattempts,
    deliveryreceiptrequested,
    deliveryreceiptrequestedname,
    description,
    directioncode,
    directioncodename,
    importsequencenumber,
    isbilled,
    isbilledname,
    isworkflowcreated,
    isworkflowcreatedname,
    messageid,
    mimetype,
    modifiedby,
    modifiedbydsc,
    modifiedbyname,
    modifiedbyyominame,
    modifiedon,
    modifiedonutc,
    notifications,
    notificationsname,
    overriddencreatedon,
    overriddencreatedonutc,
    ownerid,
    owneriddsc,
    owneridname,
    owneridtype,
    owneridyominame,
    owningbusinessunit,
    owninguser,
    prioritycode,
    prioritycodename,
    readreceiptrequested,
    readreceiptrequestedname,
    regardingobjectid,
    regardingobjectiddsc,
    regardingobjectidname,
    regardingobjectidyominame,
    regardingobjecttypecode,
    scheduleddurationminutes,
    scheduledend,
    scheduledendutc,
    scheduledstart,
    scheduledstartutc,
    sender,
    serviceid,
    statecode,
    statecodename,
    statuscode,
    statuscodename,
    subcategory,
    subject,
    submittedby,
    timezoneruleversionnumber,
    torecipients,
    trackingtoken,
    utcconversiontimezonecode
) with view_metadata as
select
    Email.ActivityId,
    Email.ActualDurationMinutes,
    dbo.fn_UTCToTzSpecificLocalTime(Email.ActualEnd,
                  us.TimeZoneBias,
                  us.TimeZoneDaylightBias,
                  us.TimeZoneDaylightYear,
                  us.TimeZoneDaylightMonth,
                  us.TimeZoneDaylightDay,
                  us.TimeZoneDaylightHour,
                  us.TimeZoneDaylightMinute,
                  us.TimeZoneDaylightSecond,
                  0,
                  us.TimeZoneDaylightDayOfWeek,
                  us.TimeZoneStandardBias,
                  us.TimeZoneStandardYear,
                  us.TimeZoneStandardMonth,
                  us.TimeZoneStandardDay,
                  us.TimeZoneStandardHour,
                  us.TimeZoneStandardMinute,
                  us.TimeZoneStandardSecond,
                  0,
                  us.TimeZoneStandardDayOfWeek),
    Email.ActualEnd,
    dbo.fn_UTCToTzSpecificLocalTime(Email.ActualStart,
                  us.TimeZoneBias,
                  us.TimeZoneDaylightBias,
                  us.TimeZoneDaylightYear,
                  us.TimeZoneDaylightMonth,
                  us.TimeZoneDaylightDay,
                  us.TimeZoneDaylightHour,
                  us.TimeZoneDaylightMinute,
                  us.TimeZoneDaylightSecond,
                  0,
                  us.TimeZoneDaylightDayOfWeek,
                  us.TimeZoneStandardBias,
                  us.TimeZoneStandardYear,
                  us.TimeZoneStandardMonth,
                  us.TimeZoneStandardDay,
                  us.TimeZoneStandardHour,
                  us.TimeZoneStandardMinute,
                  us.TimeZoneStandardSecond,
                  0,
                  us.TimeZoneStandardDayOfWeek),
    Email.ActualStart,
    Email.Category,
    Email.Compressed,
    CompressedPLTable.Value,
    Email.CreatedBy,
    Email.CreatedByDsc,
    Email.CreatedByName,
    Email.CreatedByYomiName,
    dbo.fn_UTCToTzSpecificLocalTime(Email.CreatedOn,
                  us.TimeZoneBias,
                  us.TimeZoneDaylightBias,
                  us.TimeZoneDaylightYear,
                  us.TimeZoneDaylightMonth,
                  us.TimeZoneDaylightDay,
                  us.TimeZoneDaylightHour,
                  us.TimeZoneDaylightMinute,
                  us.TimeZoneDaylightSecond,
                  0,
                  us.TimeZoneDaylightDayOfWeek,
                  us.TimeZoneStandardBias,
                  us.TimeZoneStandardYear,
                  us.TimeZoneStandardMonth,
                  us.TimeZoneStandardDay,
                  us.TimeZoneStandardHour,
                  us.TimeZoneStandardMinute,
                  us.TimeZoneStandardSecond,
                  0,
                  us.TimeZoneStandardDayOfWeek),
    Email.CreatedOn,
    Email.DeliveryAttempts,
    Email.DeliveryReceiptRequested,
    DeliveryReceiptRequestedPLTable.Value,
    Email.Description,
    Email.DirectionCode,
    DirectionCodePLTable.Value,
    Email.ImportSequenceNumber,
    Email.IsBilled,
    IsBilledPLTable.Value,
    Email.IsWorkflowCreated,
    IsWorkflowCreatedPLTable.Value,
    Email.MessageId,
    Email.MimeType,
    Email.ModifiedBy,
    Email.ModifiedByDsc,
    Email.ModifiedByName,
    Email.ModifiedByYomiName,
    dbo.fn_UTCToTzSpecificLocalTime(Email.ModifiedOn,
                  us.TimeZoneBias,
                  us.TimeZoneDaylightBias,
                  us.TimeZoneDaylightYear,
                  us.TimeZoneDaylightMonth,
                  us.TimeZoneDaylightDay,
                  us.TimeZoneDaylightHour,
                  us.TimeZoneDaylightMinute,
                  us.TimeZoneDaylightSecond,
                  0,
                  us.TimeZoneDaylightDayOfWeek,
                  us.TimeZoneStandardBias,
                  us.TimeZoneStandardYear,
                  us.TimeZoneStandardMonth,
                  us.TimeZoneStandardDay,
                  us.TimeZoneStandardHour,
                  us.TimeZoneStandardMinute,
                  us.TimeZoneStandardSecond,
                  0,
                  us.TimeZoneStandardDayOfWeek),
    Email.ModifiedOn,
    Email.Notifications,
    NotificationsPLTable.Value,
    dbo.fn_UTCToTzSpecificLocalTime(Email.OverriddenCreatedOn,
                  us.TimeZoneBias,
                  us.TimeZoneDaylightBias,
                  us.TimeZoneDaylightYear,
                  us.TimeZoneDaylightMonth,
                  us.TimeZoneDaylightDay,
                  us.TimeZoneDaylightHour,
                  us.TimeZoneDaylightMinute,
                  us.TimeZoneDaylightSecond,
                  0,
                  us.TimeZoneDaylightDayOfWeek,
                  us.TimeZoneStandardBias,
                  us.TimeZoneStandardYear,
                  us.TimeZoneStandardMonth,
                  us.TimeZoneStandardDay,
                  us.TimeZoneStandardHour,
                  us.TimeZoneStandardMinute,
                  us.TimeZoneStandardSecond,
                  0,
                  us.TimeZoneStandardDayOfWeek),
    Email.OverriddenCreatedOn,
    Email.OwnerId,
    Email.OwnerIdDsc,
    Email.OwnerIdName,
    Email.OwnerIdType,
    Email.OwnerIdYomiName,
    Email.OwningBusinessUnit,
    Email.OwningUser,
    Email.PriorityCode,
    PriorityCodePLTable.Value,
    Email.ReadReceiptRequested,
    ReadReceiptRequestedPLTable.Value,
    Email.RegardingObjectId,
    Email.RegardingObjectIdDsc,
    Email.RegardingObjectIdName,
    Email.RegardingObjectIdYomiName,
    Email.RegardingObjectTypeCode,
    Email.ScheduledDurationMinutes,
    dbo.fn_UTCToTzSpecificLocalTime(Email.ScheduledEnd,
                  us.TimeZoneBias,
                  us.TimeZoneDaylightBias,
                  us.TimeZoneDaylightYear,
                  us.TimeZoneDaylightMonth,
                  us.TimeZoneDaylightDay,
                  us.TimeZoneDaylightHour,
                  us.TimeZoneDaylightMinute,
                  us.TimeZoneDaylightSecond,
                  0,
                  us.TimeZoneDaylightDayOfWeek,
                  us.TimeZoneStandardBias,
                  us.TimeZoneStandardYear,
                  us.TimeZoneStandardMonth,
                  us.TimeZoneStandardDay,
                  us.TimeZoneStandardHour,
                  us.TimeZoneStandardMinute,
                  us.TimeZoneStandardSecond,
                  0,
                  us.TimeZoneStandardDayOfWeek),
    Email.ScheduledEnd,
    dbo.fn_UTCToTzSpecificLocalTime(Email.ScheduledStart,
                  us.TimeZoneBias,
                  us.TimeZoneDaylightBias,
                  us.TimeZoneDaylightYear,
                  us.TimeZoneDaylightMonth,
                  us.TimeZoneDaylightDay,
                  us.TimeZoneDaylightHour,
                  us.TimeZoneDaylightMinute,
                  us.TimeZoneDaylightSecond,
                  0,
                  us.TimeZoneDaylightDayOfWeek,
                  us.TimeZoneStandardBias,
                  us.TimeZoneStandardYear,
                  us.TimeZoneStandardMonth,
                  us.TimeZoneStandardDay,
                  us.TimeZoneStandardHour,
                  us.TimeZoneStandardMinute,
                  us.TimeZoneStandardSecond,
                  0,
                  us.TimeZoneStandardDayOfWeek),
    Email.ScheduledStart,
    Email.Sender,
    Email.ServiceId,
    Email.StateCode,
    StateCodePLTable.Value,
    Email.StatusCode,
    StatusCodePLTable.Value,
    Email.Subcategory,
    Email.Subject,
    Email.SubmittedBy,
    Email.TimeZoneRuleVersionNumber,
    Email.ToRecipients,
    Email.TrackingToken,
    Email.UTCConversionTimeZoneCode
from Email
    left join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
    left join UserSettingsBase us on us.SystemUserId = u.SystemUserId
    left join OrganizationBase o on u.OrganizationId = o.OrganizationId
    left outer join StringMap CompressedPLTable on
        (CompressedPLTable.AttributeName = 'compressed'
        and CompressedPLTable.ObjectTypeCode = 4202
        and CompressedPLTable.AttributeValue = Email.Compressed
        and CompressedPLTable.LangId = us.UILanguageId )
    left outer join StringMap DeliveryReceiptRequestedPLTable on
        (DeliveryReceiptRequestedPLTable.AttributeName = 'deliveryreceiptrequested'
        and DeliveryReceiptRequestedPLTable.ObjectTypeCode = 4202
        and DeliveryReceiptRequestedPLTable.AttributeValue = Email.DeliveryReceiptRequested
        and DeliveryReceiptRequestedPLTable.LangId = us.UILanguageId )
    left outer join StringMap DirectionCodePLTable on
        (DirectionCodePLTable.AttributeName = 'directioncode'
        and DirectionCodePLTable.ObjectTypeCode = 4202
        and DirectionCodePLTable.AttributeValue = Email.DirectionCode
        and DirectionCodePLTable.LangId = us.UILanguageId )
    left outer join StringMap IsBilledPLTable on
        (IsBilledPLTable.AttributeName = 'isbilled'
        and IsBilledPLTable.ObjectTypeCode = 4202
        and IsBilledPLTable.AttributeValue = Email.IsBilled
        and IsBilledPLTable.LangId = us.UILanguageId )
    left outer join StringMap IsWorkflowCreatedPLTable on
        (IsWorkflowCreatedPLTable.AttributeName = 'isworkflowcreated'
        and IsWorkflowCreatedPLTable.ObjectTypeCode = 4202
        and IsWorkflowCreatedPLTable.AttributeValue = Email.IsWorkflowCreated
        and IsWorkflowCreatedPLTable.LangId = us.UILanguageId )
    left outer join StringMap NotificationsPLTable on
        (NotificationsPLTable.AttributeName = 'notifications'
        and NotificationsPLTable.ObjectTypeCode = 4202
        and NotificationsPLTable.AttributeValue = Email.Notifications
        and NotificationsPLTable.LangId = us.UILanguageId )
    left outer join StringMap PriorityCodePLTable on
        (PriorityCodePLTable.AttributeName = 'prioritycode'
        and PriorityCodePLTable.ObjectTypeCode = 4202
        and PriorityCodePLTable.AttributeValue = Email.PriorityCode
        and PriorityCodePLTable.LangId = us.UILanguageId )
    left outer join StringMap ReadReceiptRequestedPLTable on
        (ReadReceiptRequestedPLTable.AttributeName = 'readreceiptrequested'
        and ReadReceiptRequestedPLTable.ObjectTypeCode = 4202
        and ReadReceiptRequestedPLTable.AttributeValue = Email.ReadReceiptRequested
        and ReadReceiptRequestedPLTable.LangId = us.UILanguageId )
    left outer join StringMap StateCodePLTable on
        (StateCodePLTable.AttributeName = 'statecode'
        and StateCodePLTable.ObjectTypeCode = 4202
        and StateCodePLTable.AttributeValue = Email.StateCode
        and StateCodePLTable.LangId = us.UILanguageId )
    left outer join StringMap StatusCodePLTable on
        (StatusCodePLTable.AttributeName = 'statuscode'
        and StatusCodePLTable.ObjectTypeCode = 4202
        and StatusCodePLTable.AttributeValue = Email.StatusCode
        and StatusCodePLTable.LangId = us.UILanguageId )
    cross join dbo.fn_GetMaxPrivilegeDepthMask(4200) pdm
where Email.DeletionStateCode in (0)