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.
Best Answer
Your approach is wrong.
If you would like to use the Filtered Views you have to use a Domain User with a CRM account. The application has its own security layer on top of the database security layer. So, although you have granted the user access to the database itself, the application will take care of the security regarding the his CRM privileges. See Filtered Views
Please keep also in mind that basically the only real supported usage of direct database access in Dynamics CRM is reporting (and this is restricted to the Filtered Views). You shouldn't interact directly with the database for any other reasons.