SQL Server Jobs – How to Choose the Right Job Owner

sql server

This morning I had three failed jobs (no biggy) used for custom reporting

DOMAIN\USER is no longer with the company.

Error Message

The job failed. Unable to determine if the owner
(DOMAIN\USER) of job Email Import Impacting Issues has server access
(reason: Could not obtain information about Windows NT group/user
'DOMAIN\USER', error code 0x534. [SQLSTATE 42000] (Error 15404)).

I've changed the owner to [sqlAdmin], which is one of our sqlAdmin owner accounts.

Best Answer

No, is not a good idea. There is a possible security issue underneath when using sa as owner of the job.

All jobs are ran through SQL Server Agent and it sets the security context for job execution based on the role of the user owning the job. So, if you use sa then you know what it will happen. By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account. The exception to this rule is T-SQL job steps, which execute under the security context of the job owner. If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.

So, using sa as the job owner will cause all T-SQL job steps to execute as the SQL Agent service account, which is a system administrator account. A better option is to set a non-sysadmin account as the job owner, and explicitly grant only the required database permissions to this account.

So, what I would do is to create a specific user on the DOMAIN and give that user the needed rights to execute those jobs. And that user should not be a person in the company as you could have same issue in the future, if that person goes away and the account is closed...same error again.

Related Solutions

Sql-server – Running an SSIS package owned by a domain user from SQL Server running on a local service account

My personal opinion is that option #1 is the way to go. But I see no need for you to have to grant the domain account local administrator access. It seems to me that it requires access to certain folders and files and so you could grant the domain user access to only the resources that it needs to run the package successfully. This can be done through the file/folder properties dialog box and select the security tab - there should be no need to set it for every file and folder as you could set the permissions of the parent directory and set them to override child properties.

I hope this helps you.

Sql-server – Changed server name, now maintenance plan fails

You need to change the owner of the maintenance plan. It was set up to be owned by an account that no longer exists. Not sure if it is better in your scenario to use sa or the new local admin account. This update assumes you still have the sqladmin account after the rename and that it has been added as a login to SQL Server with admin privileges.

UPDATE msdb.dbo.sysssispackages
  SET ownersid = SUSER_SID('VM-MSSQL-2008R2\sqladmin')
  WHERE ownersid = SUSER_SID('WIN-6QFI9JAK804\sqladmin');

You may also need to reset the owner of the job associated with the maintenance plan, which you can do through the UI or via the following:

EXEC msdb..sp_update_job
  @job_name = N'MaintenancePlan.Subplan_1', -- or whatever it's called
  @owner_login_name = N'VM-MSSQL-2008R2\sqladmin';

If the login hasn't been added, then execute this first:

CREATE LOGIN [VM-MSSQL-2008R2\sqladmin] FROM WINDOWS;
GO
EXEC sp_addsrvrolemember N'VM-MSSQL-2008R2\sqladmin', N'sysadmin';

In the future, be sure to have the machine properly configured / named etc. before installing SQL Server. :-)

Best Answer

Related Solutions

Sql-server – Running an SSIS package owned by a domain user from SQL Server running on a local service account

Sql-server – Changed server name, now maintenance plan fails

Related Question