You may be unable to restart the SQL Server Agent service after you repair a SQL Server 2008 installation


Symptoms


When you try to start the SQL Server Agent service after you repair a Microsoft SQL Server 2008 installation, you may be unable to restart the service.

Cause


This issue may occur if the sysadmin server role login of the SQL Server Agent service account or the SQL Server Agent service security principal (SID) is removed or missing from the SQL Server 2008 installation.

Resolution


To resolve this issue, manually add the SQL Server Agent service account or the SQL Server Agent service security principal (SID) to the sysadmin server role. The SQL Server Agent service must have the service account or the service security principal added to the sysadmin server role to function correctly. To do this, follow these steps, depending on the operating system.

Windows Server 2003 and Windows XP

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
  2. In the Connect to Server dialog box, verify the default settings, and then click Connect.
  3. In Object Explorer, right-click the server, and then click New Query to open a new Database Engine Query Editor window.
  4. In the Query Editor window, type the following Transact-SQL statement:
    EXEC sp_addsrvrolemember '<Domain>\<Account>', 'sysadmin';
    GO
    Note <Domain> represents the domain name, and <Account> represents the domain user account that is assigned to the SQL Server 2008 Agent service.
  5. Press F5 to execute the query.
  6. Close the SQL Server Management Studio window.
  7. Try to restart the SQL Server Agent service.

Windows Server 2008 and Windows Vista

For all nonclustered and clustered instances of SQL Server 2008 for which the service SID option is selected, add the SQL Server Agent service security principal to the sysadmin server role if it is not already added to the sysadmin server role.

The name of the service security principal that corresponds to the SID has the following format:
NT SERVICE\ServiceName
For example, "NT SERVICE\SQLServerAgent" represents the service principal name for the SQL Server Agent service.

The Windows user interface does not list service security principals for all the services. Therefore, you may have to manually type the name of the service security principal. To add the SQL Server Agent service to the sysadmin manually, follow these steps:
  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
  2. In the Connect to Server dialog box, verify the default settings, and then click Connect.
  3. In Object Explorer, right-click the server, and then click New Query to open a new Database Engine Query Editor window.
  4. In the Query Editor window, type the following Transact-SQL statement:
    EXEC sp_addsrvrolemember 'NT SERVICE\<ServiceName>', 'sysadmin';
    GO
    Note You must replace <ServiceName> with SQLServerAgent for the default instance or SQLAgent$InstanceName for a named instance.
  5. Press F5 to execute the query.
  6. Close the SQL Server Management Studio window.
  7. Try to start the SQL Server Agent service.
For clustered instances of SQL Server 2008 on Windows Server 2008 for which the service SID option is not selected during installation, follow the steps in the "Windows Server 2003 and Windows XP" section to add the SQL Server Agent service account.

More Information


For more information about service SIDs in SQL Server 2008, visit the follow Microsoft Developer Network (MSDN) Web site:For more information about SQL Server Management Studio, visit the following Microsoft Web site:For more information about the sp_addsrvrolemember stored procedure, visit the following MSDN Web site: