Views in restored MS Dynamics database return zero lines

Other SQL Server Topics
1

Hi,

This is what we have at hand: an old MS Dynamics database / application on SQL Server 2005 was taken out of production a while ago. Before the plug was pulled, we took a backup and restored it on our then new SQL Server 2012 test database server.

When we checked the tables, all data was there all right, but when we accessed the views, they returned no data. In MS Dynamics, nobody accesses the tables directly, all interactions with the database happen by using the views. The problem is that the views use some kind of row level filtering and return no data when run on the 2012 test server environment.

We knew about that issue and decided to postpone addressing it till it was really needed (if ever). And that time has arrived now.

What is the best way (least effort) to get those views to return data on the test server? We want all the lines, as if there was no row lever filtering.
Do we have to rewrite, clean up all the views, leaving out the row level filtering code? There are dozens of views.
Can we add the account we use for database access in one or another group in Active Directory ?
...?

With kind regards,
Wim

2

Post the script for one of those views.

3

Hi,

Below you find a small part of one filtered view. The full CREATE VIEW script is 1040 lines long.

--
-- report view for account
--
create view [dbo].[FilteredAccount] (
    accountcategorycode,
    accountcategorycodename,
    accountclassificationcode,
    accountclassificationcodename,
    accountid,
    accountnumber,
    accountratingcode,
    accountratingcodename,
    address1_addressid,
    address1_addresstypecode,
    address1_addresstypecodename,
    address1_city
) with view_metadata as 
select 
    Account.AccountCategoryCode,
    AccountCategoryCodePLTable.Value,
    Account.AccountClassificationCode,
    AccountClassificationCodePLTable.Value,
    Account.AccountId,
    Account.AccountNumber,
    Account.AccountRatingCode,
    AccountRatingCodePLTable.Value,
    Account.Address1_AddressId,
    Account.CreatedBy,
    Account.CreatedByDsc,
    Account.CreatedByName,
    dbo.fn_UTCToTzSpecificLocalTime(Account.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), 
from Account
    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 AccountCategoryCodePLTable on 
        (AccountCategoryCodePLTable.AttributeName = 'accountcategorycode'
        and AccountCategoryCodePLTable.ObjectTypeCode = 1
        and AccountCategoryCodePLTable.AttributeValue = Account.AccountCategoryCode
        and AccountCategoryCodePLTable.OrganizationId = o.OrganizationId
        and AccountCategoryCodePLTable.LangId = o.LanguageCode )
    left outer join StringMap AccountClassificationCodePLTable on 
        (AccountClassificationCodePLTable.AttributeName = 'accountclassificationcode'
        and AccountClassificationCodePLTable.ObjectTypeCode = 1
        and AccountClassificationCodePLTable.AttributeValue = Account.AccountClassificationCode
        and AccountClassificationCodePLTable.OrganizationId = o.OrganizationId
        and AccountClassificationCodePLTable.LangId = o.LanguageCode )
...
    left outer join StringMap TerritoryCodePLTable on 
        (TerritoryCodePLTable.AttributeName = 'territorycode'
        and TerritoryCodePLTable.ObjectTypeCode = 1
        and TerritoryCodePLTable.AttributeValue = Account.TerritoryCode
        and TerritoryCodePLTable.OrganizationId = o.OrganizationId
        and TerritoryCodePLTable.LangId = o.LanguageCode )
    cross join dbo.fn_GetMaxPrivilegeDepthMask(1) pdm
where Account.DeletionStateCode in (0) 
    and
    (
    -- privilege check
    pdm.PrivilegeDepthMask is not null and
    (
    -- object owned by the user
    --
    Account.OwningUser = u.SystemUserId
    -- role based access
    or 
    exists
(
	select 
		1
	where
	(
		-- deep security
		((pdm.PrivilegeDepthMask & 0x44) != 0) and 
		Account.OwningBusinessUnit in (select biz.SubBusinessId from BusinessUnitMap as biz where biz.BusinessId = u.BusinessUnitId)
	) 
	or
	(
		-- global security
		((pdm.PrivilegeDepthMask & 0x88) != 0) and 
		Account.OwningBusinessUnit is not null 
	) 
	or
	(
		-- local security
		((pdm.PrivilegeDepthMask & 0x22) != 0) and 
		Account.OwningBusinessUnit = u.BusinessUnitId
	)
)

    -- object shared to the user
    or 
    Account.AccountId in 
    (
        select  POA.ObjectId from PrincipalObjectAccess POA 
        join SystemUserPrincipals sup on POA.PrincipalId = sup.PrincipalId
            where sup.SystemUserId = u.SystemUserId and
                POA.ObjectTypeCode = 1 AND 
                ((POA.AccessRightsMask | POA.InheritedAccessRightsMask) & 1)=1
    )
    )
    )

GO
</Code>