Reset sa password for MS sql server and Sybase ASE

It may happen that you may not be able  to access some running sql server at all. You may forget to assign administrators to sql server as administrator or you forget sa password.

What you need is to have at least one account with windows administrator' privilege and you can add any windows account access to sql server. Please note that windows authentication is always possible, so you may just assign admin right for sql server with any windows account and reset sa password if necessary.

Before you re-install the sql server, just follow these steps.

Assumption:

•     sql server installation is complete and is sql server service is running correctly.

•     Forgot sa password, your known window account does not have permission to connect to sql server.

•     Have a window account which has admin right, or at least some one can give you one account with this privilege.

Here are the steps to take

•     step 1: start sql server in minimum configuration mode. For example:

            for default instance
        cd  "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\"
        or for SQL2008 instance
        cd "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\"
        sqlservr.exe -f (or sqlservr.ex -m)
        Note: make sure you "Run as administer" from DOS!
        Have a window account which has admin right, or at least some one can give you one account with this privilege.

•    step 2: connect to sql server (using the same account which started the sql server from step 1)

        list all sql server running "near you" or "at the windows network"
        sqlcmd -L
        sqlcmd -E -S MSSQLSERVER (or the correct sql server instance name)

•    step 3: add your windows account or build in admin account as sql server administrator

               CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
                GO
                EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'
                GO

•         step 4: configure all logins and remove logins which are not necessary any more.

P.S.
            Different way to start up sql server manually.
            1) net start:
        -- default instance name/service name for sql server
        -- net stop mssqlserver
        -- net start mssqlserver
      2) using sqlservr.exe
    
      reference:


        /**
        method 1
        http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/
        adding -m; in start parameter
        */

        /* method 2
        (sqlcmd is better than osql)
        find the list of sql server instance
        sqlcmd -L
        -- connect
        sqlcmd -E -S sql_server_instance
        */

                change sa password with t-sql:
        USE [master]
        GO
        ALTER LOGIN [sa] WITH PASSWORD=N''
        GO
        -- this will be removed in future version
        sp_password @old=NULL, @new=N'', @loginame=N'sa'
        go

You may think of change sql server login mode, but you will always have window authentication anyway.
But in case you want to change the security mode after installation is to stop SQL Server and set the appropriate registry key for your installation:

(from MSDN, this seems for sql server 2000)

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode
Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode
to 0 or 2 for mixed-mode or 1 for integrated. (Integrated is the default setup for the SQL Server 2000 Data Engine.)

Note The 0 and 2 values have the same functionality. They both set the security to mixed-mode.

Another note, to reset sa password for Sybase ASE, you can start up server with some optional.

http://www.sybaseteam.com/recover-sa-password-sybase-ase-step-by-step-t-933.html

The following is copied from the url above:
Recover sa password in Sybase ASE - Step by Step process

Recover sa password in Sybase ASE - Step by Step process

If the sa password is lost, the step by step process mentioned below can be followed to recover the ‘sa’ password.

Step 1. Shutdown the server - kill the processes at OS level as it is not possible to shutdown the server
Step 2. Make a copy of RUN_ file say. RUN__backup
Step 3. Edit the RUN_ file to include an argument -psa . Including "-psa" to the RUN file will generate a new password for the 'sa' login. After editing , the RUN_ file looks as below.

----------------------------------------------------------------------------------
$cat RUN_TEST_SYBASE15
#!/bin/sh
#
# ASE page size (KB): 2k
# Master device path: /usr/local/sybase15/sybdev/data/master.dat
# Error log path: /usr/local/sybase15/ASE-15_0/install/TEST_SYBASE15.log
# Configuration file path: /usr/local/sybase15/ASE-15_0/TEST_SYBASE15.cfg
# Directory for shared memory files: /usr/local/sybase15/ASE-15_0
# Adaptive Server name: TEST_SYBASE15
#
/usr/local/sybase15/ASE-15_0/bin/dataserver \
-d/usr/local/sybase15/sybdev/data/master.dat \
-e/usr/local/sybase15/ASE-15_0/install/TEST_SYBASE15.log \
-c/usr/local/sybase15/ASE-15_0/TEST_SYBASE15.cfg \
-M/usr/local/sybase15/ASE-15_0 \
-sTEST_SYBASE15 \
-psa \

------------------------------------------------------------------------------------

Step 4. Start the Sybase server using the command startserver.The password will be printed out on the console as shown below( please note that the sa password will not be written into error log) .Look for it on the console.

$startserver -f RUN_TEST_SYBASE15
00:00000:00000:2010/05/05 16:13:55.69 kernel SySAM: Checked out license for 1 ASE_CORE (2009.1231/permanent/19B3 47BC 0B7E 8DC3).
.............................................................
.............................................................
00:00000:00001:2010/05/05 16:14:01.28 server Completed REDO pass for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.28 server Recovery of database 'sybsystemprocs' will undo incomplete nested top actions.
00:00000:00001:2010/05/05 16:14:01.28 server Started recovery checkpoint for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.30 server Completed recovery checkpoint for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.32 server Started filling free space info for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.34 server Completed filling free space info for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.36 server Started cleaning up the default data cache for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.36 server Completed cleaning up the default data cache for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.37 server Checking external objects.
00:00000:00001:2010/05/05 16:14:01.43 server The transaction log in the database 'sybsystemprocs' will use I/O size of 2 Kb.
00:00000:00001:2010/05/05 16:14:01.44 server Database 'sybsystemprocs' is now online.

New SSO password for sa:yjyhzychbtafk8

00:00000:00008:2010/05/05 16:14:01.48 kernel.......................................
00:00000:00008:2010/05/05 16:14:01.48 kernel.......................................
----------------------------------------------------------------------------------------

Step 5. Login as sa user using the above password.
Step 6. Change the sa password using sp_password command.

$isql -Usa -w132 -c -Pyjyhzychbtafk8
1> sp_password yjyhzychbtafk8,qwerty4321
2>
Password correctly set.

(return status = 0)
1>

Step 7. Remove the -psa argument in RUN_ file. / Copy the RUN__backup file to RUN_ file.