Microsoft KB Archive/937682

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

Article ID: 937682

Article Last Modified on 11/20/2007

-

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

-



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:  Log on to Windows by using the account of a Windows user who is a member of the local Administrators group. Stop the SQL Server service. At a command prompt, start the instance in single-user mode. To do this, follow these steps:  At a command prompt, change to the following folder:

\Microsoft SQL Server\ \MSSQL\Binn

Notes   is a placeholder for the folder in which SQL Server 2005 is installed.  is a placeholder for the folder for the instance. </li> If the instance is a named instance of SQL Server 2005, run the following command:

sqlservr.exe -s -m -c

If the instance is the default instance of SQL Server 2005, run the following command:

sqlservr.exe -m -c

</li></ol> </li> Use the Sqlcmd utility (Sqlcmd.exe) to connect to the instance. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> Start a command prompt.</li> If the instance is a named instance of SQL Server 2005, run the following command:

sqlcmd -S \ 

If the instance is the default instance of SQL Server 2005, run the following command:

sqlcmd -S 

</li></ol> </li>  At a command prompt, run the following Transact-SQL statement. sp_addsrvrolemember '<Login>', 'sysadmin' GO Note  is a placeholder for the Windows user whom you want to add to the sysadmin fixed server role. </li></ol>

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.

<div class="references_section">