SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Testing SSAS Security in Excel

There are many posts available that describe how to test Analysis Services security from inside the development environment.  Today I would like to share a nice way to do this from excel.  When you create a connection to your Analysis Services database in Excel it creates an .odc file.  This file is used to store the connection string information about your connection.  This can also be deployed to SharePoint when using Excel Services.

To change what user is being impersonated in Excel (after initially creating a connection) you will need to go to the Data tab and select Connections.  That will open the screen below:

Select Properties on the connection to modify.  Then on the definition tab add the highlighted text with the desired role or username to test.


Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


No comments.

Leave a Comment

Please register or log in to leave a comment.