What permissions are needed to create new objects in an Analysis Services database?

 

To make a new dimension or any object in an SQL Server 2008 Analysis Services database, the user must be in a role that has “Full control ( Administrator)” permissions. I confirmed this by breaking my role, getting an error, and researching the source code which throws the error to see what conditions it checks.

The product checks for the current user’s sessions to be an Administrator within the database when creating new objects  by saving/deploying them from BIDS to the Analysis Services.

image

IF the user does not have Full control, then when deploying or saving the new object, the following error may appear

TITLE: Microsoft Visual Studio

Either the 'machinename\username' user does not have permission to create a new object in 'databasename', or the object does not exist.

image

 

Test Methodology:

1. Make a new role under a database of your choosing in Analysis Services in SSMS object explorer.

a. On the general tab, name the role, and check the two boxes “process database” and “read definition”

b. On the membership tab, add a local windows account on my machine machinename\accountname

 

2. Using the command prompt, launch BIDS as the new local account that is a member of that new role to test my ability to create new dimensions.

Runas /user:machinename\accountname "C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"

Connect to the Server and to the database in BIDS

Note I can see the definitions of dimensions since I have read definition permission.

 

3. In BIDS Solution Explorer, expand Dimensions. Right click to run the wizard to create a new dimension

Step through the configuration of the dimension.

Error

TITLE: Microsoft Visual Studio

------------------------------

Either the 'machinename\username' user does not have permission to create a new object in 'databasename', or the object does not exist.

------------------------------

BUTTONS:

OK

------------------------------

The dimension is still visible in BIDS, but cannot be saved/deployed to the server.

 

4. Added permissions so the new dimension can be saved.

a. Tried - On the role from #1 above, click on the cube tab. On the cube, set the access to Read/Write. That was not sufficient to save a new dimension.

b. Tried – On the role from #1 above, edit the general tab, and check the 3rd box - “Full control” checkbox. Now the save worked. Now all other tabs in the security are grayed out.

 

Note: The equivalent XMLA that is run under the covers for creating a dimension is an ALTER with AllowCreate=True to make the new objects.

<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">

<Alter AllowCreate="true" ObjectExpansion="ExpandFull">

<Object>

<DatabaseID>dbname</DatabaseID>

<DimensionID>dimName</DimensionID>

</Object>

<ObjectDefinition>

</ObjectDefinition>

</Alter>

</Batch>