Hi
the problem is that the function fn_UTCToLocalTime executes another function fn_FindUserGuid
which returns the guid of the current connected user.
After getting the GUID, the procedure gets the settings from the CRM table "systemuser"
since you are using an SQL user (not active directory) the "systemuser" table will return null (since the user is not a CRM user)
as a workaround I have modified the "fn_FindUserGuid" function in the CRM databse by adding
the below to the end of the function:
if @userGuid is null
begin
if SYSTEM_USER = 'SQLuser'
begin
select @userGuid = s.SystemUserId
from SystemUserBase s
where s.DomainName = 'Domain\Username'
end
end
replace SQLuser by the username you are using to connect to the database , in my case it was "sa"
and 'Domain\Username' by any CRM username 'MyCompany\CRMadmin'
it should work now :)
the function will look like
ALTER function [dbo].[fn_FindUserGuid] ()
returns uniqueidentifier
as
begin
declare @userGuid uniqueidentifier
--- test whether the query is runing by priviledged user with user role of CRMReaderRole
--- if it is dbo, we trust it as well.
--- There is an issue in SQL. If the user is a dbo, if it not member of any role
if (is_member('CRMReaderRole') | is_member('db_owner')) = 1
begin
select @userGuid = cast(context_info() as uniqueidentifier)
end
if @userGuid is null
begin
select @userGuid = s.SystemUserId
from SystemUserBase s
where s.DomainName = SUSER_SNAME()
end
if @userGuid is null
begin
if SYSTEM_USER = 'sa'
begin
select @userGuid = s.SystemUserId
from SystemUserBase s
where s.DomainName = 'MyCompany\CRMadmin'
end
end
return @userGuid
end
