Export (0) Print
Expand All
0 out of 3 rated this helpful - Rate this topic

Give Others Ownership of a Job

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

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 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.

[Top]

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.

[Top]

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
    

[Top]

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 more information, see SQL Server Management Objects (SMO). For example code, see Scheduling Automatic Administrative Tasks in SQL Server Agent.

[Top]

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.