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.
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.
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>
Comments
- Anonymous
September 23, 2012
Try this It woks 1 .The default account, the service is running as NT ServiceMSSQLServerOLAPService.
In Management Studio, connect to the database engine instance. 3. Expand the Security folder, right-click Logins and select New Login. 4. On the General page, in Login name, type NT ServiceMSSQLServerOLAPService (or whatever account the service is running as). 5. Click User Mapping. 6. Select the checkbox next to the AdventureWorksDW2012 database. Role membership should automatically include db_datareader and public. Click OK to accept the defaults.