At work we have Microsoft Dynamics CRM 4.0 installed and employees use their LAN login to log into CRM's web interface.
We've added a SQL Server-based custom login (to the server and db *_MSCRM) with the db_datareader role. The new login can select from views, however it returns an empty result set. If I log into SQL Server Management Studio using my LAN ID and run sql against the same view, I get the expected data.
Any ideas what is missing or wrong?
UPDATE
I was ultimately able to circumvent this issue. The issue was that the sql behind the view was linking to a custom user table which prevented my sql sever login from returning any data, since it was not in this custom user table (SystemUserBase)
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
I guess Microsoft designed it this way so that the product is self-servicing, whereas you do not need a DBA to add users to the database. Users are administered through the application. And security is enforced through the views.
In any case, I was able to get around it by passing the SystemUserId guid for my LAN ID, while being logged in as the generic sql login. There's a function which returns the guid for the currently logged in LAN ID: print dbo.fn_FindUserGuid();
. Then I just ran a modified copy of the view sql:
declare @user_guid char(36);
set @user_guid = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'; /* obfuscated */
...
left join SystemUserBase u on (u.SystemUserId = @user_guid and u.IsDisabled = 0)
...
I also had to remove any references to any functions the generic login didn't have execute rights to (i.e. dbo.fn_UTCToTzSpecificLocalTime
). Or I could grant executes rights for each one.
Alternatively, I may investigate creating an entry in the SystemUserBase
table for my generic login, so that I'm not tied to my LAN guid.
UPDATE 2
I was able to eliminate the need for my LAN guid (SystemUserBase.SystemUserBase
). I simply removed all of the permissions-related where clauses. Then, all that's needed for the for the StringMap lookups is the OrganizationBase.OrganizationId
guid, and the OrganizationBase.LanguageCode
which In our case is en-us or 1033.
If I didn't care about the lookups, I wouldn't need anything. I would just go against the tables directly without the StringMaps or permissions where clauses.