Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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

Filtered views are fully compliant with the Microsoft Dynamics CRM security model. When you run a report that obtains data from filtered views, the Microsoft Dynamics CRM security role determines what data you can view in the report.

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.

share|improve this answer
    
There's no way to access those views from a SQL Server login by granting a certian set of permissions or using a certian role? –  Edward J. Stembler Aug 17 '12 at 19:45
    
@EdwardJ.Stembler please see the update. Does this answer your question? –  ccellar Aug 17 '12 at 22:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.