This is a somewhat obscure topic, but I hope that others (if they ever find themselves in this position) find it useful!
In Analysis Services you can have administrator rights over an entire server instance, as well as administrator rights over particular databases on the server instance.
To give an example, let’s say that an organisation has a structure where there is one server administrator responsible for managing the Analysis Services server, and many database administrators responsible for developing, deploying, and updating particular database models hosted on that server.
This situation may become more common as organizations move their infrastructure to the cloud and consolidate server deployments to Azure Analysis Services. When this happens there may be multiple developers working on different models all hosted by a single server that supports a specific country/region.
In this example there are limitations that these database admins may face because they are not also server admins.
To get straight to the point, listed below are the main limitations that I have found so far, as well as any workarounds.
Limitation 1: Database admins cannot create new databases
I wasn’t sure whether to include this limitation or not because it’s obvious.
Without the database existing, it follows that there is no database for you to have database administrator access over.
There is no workaround.
In this scenario you just need a server administrator to create a new database and create a role within it that has administrator access, and then add you to that role.
Limitation 2: Database admins cannot deploy a model to the server using restricted data source settings such as impersonation mode (except when using BISM-Normalizer)
I’m not sure if this is a bug within Analysis Services or working as intended. But the result seems to be that for the vast majority of the time, you can’t deploy your model using SSDT (or Tabular Editor!).
The problem you will encounter is that you are unable to deploy updated model metadata via SSDT to a database that you are the administrator over if the metadata contains an ImpersonationMode (e.g. the data source settings include impersonation of another account).
Information on impersonation from Microsoft docs here, a forum post where the “workaround” is to just make them a server admin here, and another sort of workaround here, but may not necessarily apply to Tabular.
The error message looks like this:
------ Deploy started: Project: VSProject, Configuration: Development x86 ------
Cannot deploy metadata. Reason: Failed to save modifications to the server.
Error returned: 'The ImpersonationInfo for datasource contains an ImpersonationMode that can only be used by a server administrator.
Unfortunately, you also receive the same error when deploying from Tabular Editor, which hints at the fact that these two tools may have similar deployment methods.
Tabular Editor also cannot deploy without server admin rights when using ImpersonationMode settings[/caption]
Note that there also appears to be a bug with Tabular Editor in that AAS role members cannot be deployed to an AAS model. If you test this out for yourself make sure you don’t include role members, otherwise you may receive a different error before you get to the ImpersonationMode error.
The workaround: here’s where it gets interesting, you can actually avoid this error message by deploying via BISM-Normalizer. I’m not sure yet what black magic is used to make this work, but it’s obviously related to the deployment methods. Paging @_christianWade if he’s able to shed any light on this!
Deploying metadata without server admin rights via BISM-Normalizer is successful! However server profile traces are not allowed.
I’ve had a look at the deployment trace files and can’t seem to figure out how BISM-Normalizer is doing it, but I’ll keep exploring and will update this post if I find anything interesting. I have a sinking feeling it might require looking through the SSAS Tabular protocol…
A quick tip: make sure that if you want a full deployment via BISM-Normalizer that you include everything you mean to. (e.g. including partitions / perspectives / roles / members / etc.)
Limitation 3: Database admins cannot set up server-side automated refreshing of data (aka “processing”).
There are many different ways to automate the “processing” of your model. Processing is the term used to refresh the data and perform static calculations such as re-calculating DAX columns or tables.
In Azure, from what I’ve seen so far, most methods rely on adding a service credential to the list of server administrators or using a server administrator’s credentials. (while on this topic, running your automation through a runbook means you can only refresh as often as once per hour, although there are other methods in AAS to have quicker processing).
In on-premises I believe it relies on the SQL Server Agent using proxy credentials of a server administrator.
Workaround: Get around this by using remote process execution via PS + Task Scheduler. I made a post last week on how to do this.
Limitation 4: Database admins have limited query tracing capability
Note that when you use BISM-Normalizer to deploy your model, you will receive the error message (above) after it has been deployed saying that a server trace was unable to be captured.
The reason for this error message is because only server admins can capture trace events…sort of.
If you connect SQL Server Profiler to a server you might have to wait for a minute or two but it should eventually connect in a somewhat limited fashion, and you actually can capture what appear to be partial traces of queries sent to the database on the server. (Similar to how you can see most “DISCOVER” Dynamic Management Views).
This makes sense because if you’re querying a server, you should only be able to see the queries that relate to the databases that you administer, however there are also many events that relate to the server in general, such as connection attempts with the server itself, as well as queries to other databases.
Note that when using SQL Server Profiler to connect to Analysis Services you only specify the server, not the actual database, although the database can be filtered using the row filter settings once you’re connected.
Sadly, part of not being able to perform traces means that DAX Studio runs into fatal errors or displays the same error message as when you deployed the model using BISM-Normalizer.
This means that testing/optimizing your queries on the production instance will not work, and instead you’ll just have to deploy it to a local developer instance or another server in which you have administrator rights.
Note that you can still get most Dynamic Management Views, with the exception of some DISCOVER views that require server administrator permissions.
Closing remarks and a guess at the future
Hopefully in the future SSDT and Tabular editor can overcome this deployment issue, however the workaround isn’t that painful and using BISM-Normalizer for deployments (especially to a production database) is probably a best practice anyway!
In regards to how this relates to Power BI, hopefully the “publishing”(deployment) of Power BI “datasets”(models) to PBI “workspaces”(server instances) is done in a similar way to BISM-Normalizer instead of the current PBI publishing method or the method that SSDT and Tabular Editor use.
To get a glimpse at this, see the MAQ software ALM toolkit for Power BI, and this display by Amir Netz at the Business Applications Summit 2018, starts at 26:30.
