Enable the sa account or set back sysadmin permission on an account

issue: The domain account used to install sql server 2008 had full control of sql. However, for some reason that account cannot really do anything right now as the serverrole permissions.


solution:I researched and found out that i can change to mixed mode and log in using the sa account.

1) Log on to the server using the local admin account

2) Stop SQL Server Services & Moss Services (Purpose: to ensure that no
service is trying to connect once SQL Server is in Single user mode)

3) Set LoginMode to 2 in the Registry
a. Command Prompt type "regedit"
b. HKEY_Local_Machine\Software\Microsoft\Microsoft SQL
Server\MSSQL10.MSSQLSERVER
c. Click on MSSQL Server -->To the right click on Login mode
d. Change to 2 (To enable Mixed Mode Authentication in SQL)

4) (Command Prompt 1) Open Command Prompt (Right click and select
Run as Administrator) --> Navigate to the instance where the
Binn folder is located
a. D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn
b. Type sqlservr.exe -m -f
c. Enter
(-m - single user mode)
(-f - minimal config mode)

NOTE: Have to see: SQL Server is now ready for client connect Recovery is complete

5) LEAVE WINDOW OPEN

6) (Command Prompt 2)Open a new command prompt(Right click and
select Run as Administrator) --> Navigate to the C prompt
a. Type osql -S servername -E
b. Alter login sa ENABLE
c. GO
d. Alter login sa WITH PASSWORD = 'installer'
e. GO

NOTE: Once there isan't an error it will go to 1> (this enables you to enter a new query)

f. Type EXIT
g. Close Window
(osql same as sqlcmd)
(-S to present the server name)
(-E Trusted Windows Connection)

7) Open Management Studio
a. Select SQl Server Authentication
b. Login sa
c. Enter the password that was set 'installer'
d. Connect
e. Expand Security --> Expand Logins
f. Right click properties for an account --> Server Roles Select sysadmin

8) Close Command Prompt 1

9) Log off server as local admin; Log in as the changed account

10) Start back SQL Services & Moss Services

11) From Management Studio, Disable sa account
a. Expand Security --> Expand Logins
b. Right click sa --> Properties
c. Select Status --> Login Disabled

12) Change back Log In Mode to 1 in the registry
a. Command Prompt type 'regedit'
b. HKEY_Local_Machine --> Software --> Microsoft --> Microsoft SQL Server
--> MSSQL10.MSSQLSERVER
c. Click on MSSQL Server --> To the right click on Login mode
d. Change to 1