August 17, 2017

Executing SQL queries in CRM database on behalf of another user

If you are using filtered views in SSMS it is not possible to see what data will be returned for other users. But there is a way!

The GUID of the user is stored in the SQL CONTEXT_INFO. All you have to do is to run this piece of code:

DECLARE @binUserGuid VARBINARY(128)
DECLARE @userGuid UNIQUEIDENTIFIER
SET @userGuid = 'GUID of the user you want to test'
SET @binUserGuid = CAST(@userGuid AS VARBINARY(128))

SET CONTEXT_INFO @binUserGuid

From now on you will be able to query the data as if you would be logged in as another user.

To clear the context info simply execute

SET CONTEXT_INFO 0x

I hope this helps those who are testing SQL queries for the CRM reports.