0

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.

2 Answers 2

0

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.

2
  • 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? Aug 17, 2012 at 19:45
  • @EdwardJ.Stembler please see the update. Does this answer your question?
    – ccellar
    Aug 17, 2012 at 22:29
2

To use Filtered Views you'll need to set the CRMReaderRole on the login, then before you query the data you need set CONTEXT_INFO

One Time Setup

USE [CRM_MSCRM]
GO
EXEC sp_addrolemember N'CRMReaderRole', N'your sql login here'
GO

then do this before you query the filtered views

SELECT @uid = SystemUserId FROM CRM_MSCRM.dbo.SystemUserBase WHERE FullName = 'CRM USER TO IMPERSONATE' 
SET CONTEXT_INFO @uid

NOTE: This will work with a SQL User, but it wont work with a linked server. Tested on CRM 2011 UR 17

1
  • You can use openquery to set the CONTEXT_INFO on a linked server but it gets kinda messy because you can't use parameters Jul 29, 2014 at 19:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .