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?
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.
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.