In Sql 2005, to simplfy management of the service account's permissions against SQL itself (specifically with regard to changing which account SQL is running under) the product team started creating local Windows security groups, of the form:
computername\SQLServer2005MSSQLUser$computername$MSSQLSERVERThis group is configured by the installer to contain the service account (eg Network Service), and a corresponding SQL login is created (for the windows group) granting sysadmin rights:
I'm a big fan of running services as Network Service. Not having to create explicit service accounts means less admin overhead (both creation, and password expiry maintanance) and a lower overall attack footprint for your enterprise. But there is a downside - a lack of permissions isolation between services also running as Network Service on the same box. In this case, because of the above, anything else on that box that runs as Network Service is automatically sysadmin on your SQL instance.
In Sql 2008 on Windows 2008 R2 the situation is a bit different, because Windows 2008 R2 introduces so-called virtual accounts. I'm still a bit hazy on these, but one of the things this enables you to do is grant permissions to a service without knowing which account it's running under. The actual permissions the service has at runtime are then the union of permissions explicitly granted to the service account as well as the permissions granted to the service itself.
Which is cool. If a bit freaky at first.
So whilst Sql 2008 still has one of those local Windows groups created for it's service accounts, the contents of this are now, somewhat tautologically:
...and at the database level, the group is actually ignored, and the login (and SA grant) is given directly to itself, not the group:
(note: i've got SQL 2008 as a non-default instance, hence the specific naming. But you get the idea).
What does all this mean? Put simply (and somewhat recursively):
- Only the SQL 2008 service itself is setup as an adminstrator on the SQL 2008 service. The principal (service account) that runs it is not - by itself - an administrator on that instance.
- It is no longer the case that other applications running under the SQL Server service account are sysadmins on any SQL instances running under those same credentials.
In this case the problem is the solution: I can just go an add a grant for the virtual service account for Analysis Services, give it enough SQL permissions to do what it needs and the problem goes away.
More on virtual accounts from the Sql 2012 doco, and from Technet articles Managed Service Accounts (MSAs) Versus Virtual Accounts in Windows Server 2008 R2 and What's New in Service Accounts