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

Article translations Article translations
Article ID: 937682 - View products that this article applies to.
Expand all | Collapse all

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:
http://msdn2.microsoft.com/en-us/library/ms186320.aspx
For more information about considerations that apply when you run SQL Server 2005 on Windows Vista, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/Aa905868.aspx
For more information about how to connect to SQL Server 2005 from Windows Vista, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/bb326612.aspx

Properties

Article ID: 937682 - Last Review: November 20, 2007 - Revision: 1.4
APPLIES TO
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
Keywords: 
kbexpertiseadvanced kbhowto kbinfo KB937682

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com