February 26, 2017 at 9:39 pm
I have a job which performs index maintenance (ALTER INDEX) on any/all databases based on level of fragmentation.
The Job is both owned and EXECUTED AS 'SQLAdmin' which is an SQL user who is a member of the sysadmin role.
I'm limiting the testing of this job to a single database which happens to be the same database where the SQL for the job resides - IE I'm attempting to reindex tables in the same database where the reindexing stored procedure being executed by the job resides.
The job was initially failing with the error: Cannot find the object <DBname>.dbo.<Tablename> because it does not exist of you do not have permissions.
As <tablename> exists I interpreted this as some sort of permission error and specified the SQLAdmin in EXECUTE AS parameter against the job step. (I also set TRUSTWORTHY to ON for this DBName).
The job still fails with the error message: EXECUTE AS USER failed for the requested user 'SQLAdmin' in the database '<DBName>'
I read somewhere that the database owner & job owner need to be the same user - so I made that adjustment also. I also explicitly granted ALTER permissions to the table in question for this user out of curiosity.
sp_helpuser now shows: username: dbo, rolename: db_owner, loginname: SQLAdmin for this user.
Any advice regarding setting up an adequate admin/maintenance user that can perform this task would be much appreciated.
February 27, 2017 at 7:01 am
I'm not sure, but I had thought that the execution context for the Agent job mattered, and as Agent jobs run under the auspices of the Agent Service's service account, that account would have to have the rights. You really shouldn't need to add explicit permissions to do something like this, but I suspect you would need to have the account that the Agent Job service runs under have the necessary permissions. Any experts who know for sure, please correct me.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 27, 2017 at 9:49 am
That is my understanding as well. If the job owner has sysadmin-level authority, then the job runs under the Agent's account.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 27, 2017 at 2:10 pm
Maybe the SIDS don't match between sys.databases and sys.database_principals. You could check with the following:
USE <DBNameWithErrors>
GO
SELECT owner_sid
FROM sys.databases
WHERE database_id=DB_ID()
SELECT sid
FROM sys.database_principals
WHERE name=N'dbo'
Sue
February 27, 2017 at 5:45 pm
sgmunson - Monday, February 27, 2017 7:01 AMI'm not sure, but I had thought that the execution context for the Agent job mattered, and as Agent jobs run under the auspices of the Agent Service's service account, that account would have to have the rights. You really shouldn't need to add explicit permissions to do something like this, but I suspect you would need to have the account that the Agent Job service runs under have the necessary permissions. Any experts who know for sure, please correct me.
Quite right - thank you for setting me straight on that. I've chosen to run the job under lower privileged account by setting the job owner as such.
Thanks again.
February 27, 2017 at 5:45 pm
ScottPletcher - Monday, February 27, 2017 9:49 AMThat is my understanding as well. If the job owner has sysadmin-level authority, then the job runs under the Agent's account.
Thanks for the confirmation, problem now resolved.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply