Edit

Give Others Ownership of a Job

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Important

On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.

This topic describes how to reassign ownership of Microsoft SQL Server Agent jobs to another user.

Before You Begin

Limitations and Restrictions

To create a job, a user must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role. A job can be edited only by its owner or members of the sysadmin role. For more information about the SQL Server Agent fixed database roles, see SQL Server Agent Fixed Database Roles.

You must be a system administrator to change the owner of a job.

Assigning a job to another login does not guarantee that the new owner has sufficient permission to run the job successfully.

Security

For security reasons, only the job owner or a member of the sysadmin role can change the definition of the job. Only members of the sysadmin fixed server role can assign job ownership to other users, and they can run any job, regardless of the job owner.

Note

If you change job ownership to a user who is not a member of the sysadmin fixed server role, and the job is executing job steps that require proxy accounts (for example, SSIS package execution), make sure that the user has access to that proxy account or else the job will fail.

Permissions

For detailed information, see Implement SQL Server Agent Security.

Using SQL Server Management Studio

To give others ownership of a job

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

  2. Expand SQL Server Agent, expand Jobs, right-click the job, and then click Properties.

  3. In the Owner list, select a login. You must be a system administrator to change the owner of a job.

    Assigning a job to another login does not guarantee that the new owner has sufficient permission to run the job successfully.

Using Transact-SQL

To give others ownership of a job

  1. In Object Explorer, connect to an instance of the Database Engine, and then expand that instance.

  2. On the toolbar, click New Query.

  3. In the query window, enter the following statements that use the sp_manage_jobs_by_login (Transact-SQL) system stored procedure. The following example reassigns all jobs from danw to françoisa.

    USE msdb ;  
GO  

EXEC dbo.sp_manage_jobs_by_login  
    @action = N'REASSIGN',  
    @current_owner_login_name = N'danw',  
    @new_owner_login_name = N'françoisa' ;  
GO

Using SQL Server Management Objects

To give others ownership of a job

  1. Call the Job class by using a programming language that you choose, such as Visual Basic, Visual C#, or PowerShell. For example code, see Scheduling Automatic Administrative Tasks in SQL Server Agent.

See Also

Implement Jobs
Create Jobs