I am using Windows 7 on my laptop and needed to test out dimensional level security using Power Pivot\Excel 2010 that connected to a SQL Server 2012 Analysis Services cube. The cube was setup to filter on a business unit dimension. Users could only view measures in which they belonged to that particular business unit. I needed a way to connect as different domain users to verify the dimensional security in SSAS was properly setup. I wanted to connect to the client domain, that I was not a member of, with my laptop that belonged to my home office domain. Essentially, I wanted to connect using am\tom.puch while logged on to my laptop as pla\tpuch.
I came across an excellent posting by James Kovacs where he connects to SQL Server database engine using SQL Server Management Studio using a different domain user than the one he was logged on to this local machine. Devin Knight also had a nice posting where he used the EffectiveUserName property in the connection string I wanted to demonstrate how James Kovacs’ technique could work for Power Pivot/Excel 2010 when connecting to a SQL Server Server 2012 Analysis Service Cube.
I opened PowerShell and used the following command.
runas /netonly /user:AM\tom.puch "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe"
Excel started up. I then confirming the data connection in Excel, I can now see I am connecting with am\tom.puch.
A quick check of SQL Server Profiler confirms that the correct account is used.
