How to add a Windows user to the sysadmin fixed server role in SQL Server 2005 as a failure recovery mechanism


INTRODUCTION


This article describes how to add a Microsoft Windows user to the sysadmin fixed server role in Microsoft SQL Server 2005. To do this, the method that is described in this article uses the account of a Windows user who is a member of the local Administrators group.

Important Use the method that is described in this article only as a failure recovery mechanism.

More Information


You can use the account of a Windows user who is a member of the local Administrators group to add another Windows user to the sysadmin fixed server role in SQL Server 2005. To do this, follow these steps:
  1. Log on to Windows by using the account of a Windows user who is a member of the local Administrators group.
  2. Stop the SQL Server service.
  3. At a command prompt, start the instance in single-user mode. To do this, follow these steps:
    1. At a command prompt, change to the following folder:
      SQLInstall\Microsoft SQL Server\MSSQL.X\MSSQL\Binn
      Notes
      • SQLInstall is a placeholder for the folder in which SQL Server 2005 is installed.
      • MSSQL.X is a placeholder for the folder for the instance.
    2. If the instance is a named instance of SQL Server 2005, run the following command:
      sqlservr.exe -sInstanceName -m -c
      If the instance is the default instance of SQL Server 2005, run the following command:
      sqlservr.exe -m -c
  4. Use the Sqlcmd utility (Sqlcmd.exe) to connect to the instance. To do this, follow these steps:
    1. Start a command prompt.
    2. If the instance is a named instance of SQL Server 2005, run the following command:
      sqlcmd -SComputerName\InstanceName
      If the instance is the default instance of SQL Server 2005, run the following command:
      sqlcmd -SComputerName
  5. At a command prompt, run the following Transact-SQL statement.
    sp_addsrvrolemember '<Login>', 'sysadmin'
    GO
    Note <Login> is a placeholder for the Windows user whom you want to add to the sysadmin fixed server role.
Important The method that is described in this article is a new feature in SQL Server 2005. You cannot disable this feature. However, use other methods to add a Windows user to the sysadmin fixed server role if other methods are available. For example, if you have a different login that is a member of the sysadmin fixed server role, use this different login to log on to the instance. Then, add the Windows user to the sysadmin fixed server role.

References


For more information about the sp_addsrvrolemember stored procedure, visit the following Microsoft Developer Network (MSDN) Web site: For more information about considerations that apply when you run SQL Server 2005 on Windows Vista, visit the following MSDN Web site: For more information about how to connect to SQL Server 2005 from Windows Vista, visit the following MSDN Web site: