Recover sa password

Question

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

User · Answer

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:

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

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.

User · Answer

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   Disaster Recovery  What to do when the SA account password is lost in SQL Server 2005 Is there a way I can retrieve sa password in sql server 2005 How to recover SA password on Microsoft SQL Server 2008 R2  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  lt  SQL Server 2012   and a follow-up that shows how to hack the SqlWriter service  which can work on more modern versions    Recover access to a SQL Server instance More on Recovering Access to a SQL Server Instance  And some other resources   Connect to SQL Server When System Administrators Are Locked Out  Leveraging Service SIDs to Logon to SQL Server Instances with Sysadmin Privileges

[sql] Recover sa password

Examples related to sql

Examples related to sql-server-2008-r2