37

I have a computer which was used by another employee.

SQL Server 2008 R2 was installed but I don't know the 'sa' password.

When I try to alter the login, it gives below error.

Cannot alter the login 'sa', because it does not exist or you do not have permission.

When I try to restore a database, it gives a different permission error.

(When I enter the Security --> Logins --> sa --> Properties windows authentication is disabled.)

Can I change it?

P.S: Password is not "password" :)

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Stack User
  • 1,378
  • 5
  • 19
  • 40
  • 1
    possible duplicate of [Is there a way I can retrieve sa password in sql server 2005](http://stackoverflow.com/questions/196150/is-there-a-way-i-can-retrieve-sa-password-in-sql-server-2005) – Aaron Bertrand Jul 13 '12 at 18:10

2 Answers2

47

The best way is to simply reset the password by connecting with a domain/local admin (so you may need help from your system administrators), but this only works if SQL Server was set up to allow local admins (these are now left off the default admin group during setup).

If you can't use this or other existing methods to recover / reset the SA password, some of which are explained here:

Then you could always backup your important databases, uninstall SQL Server, and install a fresh instance.

You can also search for less scrupulous ways to do it (e.g. there are password crackers that I am not enthusiastic about sharing).

As an aside, the login properties for sa would never say Windows Authentication. This is by design as this is a SQL Authentication account. This does not mean that Windows Authentication is disabled at the instance level (in fact it is not possible to do so), it just doesn't apply for a SQL auth account.

I wrote a tip on using PSExec to connect to an instance using the NT AUTHORITY\SYSTEM account (which works < SQL Server 2012), and a follow-up that shows how to hack the SqlWriter service (which can work on more modern versions):

And some other resources:

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
4

best answer written by Dmitri Korotkevitch:

Speaking of the installation, SQL Server 2008 allows you to set authentication mode (Windows or SQL Server) during the installation process. You will be forced to choose the strong password for sa user in the case if you choose sql server authentication mode during setup.

If you install SQL Server with Windows Authentication mode and want to change it, you need to do 2 different things:

  1. Go to SQL Server Properties/Security tab and change the mode to SQL Server authentication mode

  2. Go to security/logins, open SA login properties

a. Uncheck "Enforce password policy" and "Enforce password expiration" check box there if you decide to use weak password

b. Assign password to SA user

c. Open "Status" tab and enable login.

I don't need to mention that every action from above would violate security best practices that recommend to use windows authentication mode, have sa login disabled and use strong passwords especially for sa login.