0

I have an Analysis Service database which I want to process it's cube regularly using sql database engine JobAgent, I scripted my cube process and paste it as step for job and continue it's configuration exactly as written in here

https://msdn.microsoft.com/en-us/library/ff929186.aspx

when I execute this job it gives me this error

Either the 'NT SERVICE\SQLAgent$SQL2016' user does not have permission to process the 'CB_MetaOverall' object or the object does not exist

I Use SQL Server 2016

the cube exist because when I run the commands that pasted as job step on SSAS it works fine but it's problematic from SSMS JobAgent, I searched the issue and as written here https://msdn.microsoft.com/en-us/library/ms175426.aspx I created a role in my SSAS database and gave it all of the permissions see picture below

enter image description here

And in Membership tab I specified following users hoping the issue resolved see picture below

enter image description here

but it didn't work and still same error show up when I execute my job

I even added NT SERVICE\SQLAgent$SQL2016 in security tab of Analysis Service Properties see picture below but still the same enter image description here

what else can I do to fix this???

I've digged internet for this Issue and really have no Idea about it, any help would be appreciated

Codor
  • 17,447
  • 9
  • 29
  • 56
Code_Worm
  • 4,069
  • 2
  • 30
  • 35
  • I just Solved it somehow , I ran **SQL Job Agent Service** with **administrator account** instead of "NT SERVICE\SQLAgent$SQL2016" but is there any better way to solve this issue without using administrator user for job agent service because our database is sort of national database and is supposed to serve huge number of users all over the country. since security is very very important I'd rather not to change default user of job agent service – Code_Worm Sep 05 '16 at 16:08

1 Answers1

0

I fixed the error in a similar way by running the job with a user-context that is allowed to process the Analysis Services Data. This is how I have done this:

  1. Make a DOMAIN\USER administrator of SQL Server Analysis Services(or try to add it to a SQL Server Analysis Services Role that is allowed to process the data. But I have not tested that...)
  2. Give the same DOMAIN\USER the fixed-role "sysadmin" on the SQL Server.
  3. Create new credentials in SQL Server with Login Data of this DOMAIN\USER.
  4. Create a proxy User on SQL Server with the new created credentials.
  5. Select the proxy user in your SQL Server Agent Job step.

Hope it works for someone else also.

Felix

Felix Quehl
  • 744
  • 1
  • 9
  • 24