Run MDX Queries Under Specific Roles

I wanted to run some MDX queries under specific roles.  When you browse a cube in SQL Server Management Studio (SSMS), you can change your security context by using the Change User botton (1st one from the left):

Then pick the roles:

But there is no such button when you run MDX queries in SSMS.  So how do you do it?  As it turns out you can change the connection properties and add connection string parameters, as shown below:

Note that in order for this to work you need to be a server admin on SSAS.

Control Locking with SQL Full Text Search and CONTAINSTABLE

One of our systems at work uses SQL Full Text Search to let users search for products using text descriptions. However, users often complain of slow performance or even time-outs.

After some investigation, I concluded that, besides performance issues, there were also some process blocking due to SQL locks. So I not only restructured these queries and added indexes to make them more efficient, I also added "WITH (NOLOCK)" to some of the queries (where dirty reads is not an issue).

My further testing showed that locks on a table will also block Full Text Searches using CONTAINSTABLE(). However, you cannot add "WITH (NOLOCK)" when you are selecting from CONTAINSTABLE(). So, is it possible to ignore locks when using CONTAINSTABLE()?

Well, as it turns out, you can. I remembered that "WITH (NOLOCK)" is equivalent to setting the transaction isolation level to READ UNCOMMITTED. So I just set it before the CONTAINSTABLE() statement and reset it afterward, like so:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
    KEY, RANK
FROM CONTAINSTABLE(Product, Description, @SearchTerm);
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Testing confirmed that this allows the use of CONTAINSTABLE() while ignoring table locks. And as they say, voilà, problem solved.