How to reset sa password in SQL Server 2008 when forgot the sa password?
Many time, This problem comes, they have setup SQL 2008 or SQL 2008 R2 for development or QA environment, but they don't know the login sa password by which they can connect to server. In SQL Server 2005, SQL used to fixed group sysadmin with the server role BUILTIN\Administrators, that is to say, all local NT administrator automatically add a SQL Server system administrator. But since 2008 Windows BUILTIN\Administrators group is not included by default in the sysadmin server role in SQL Server.
So, follow the steps mentioned to reset SQL 2008 password for SA account if no BUILTIN\Administrators.
Step 1. Download and install MS SQL Server Password Unlocker.
Step 2. Run MS SQL Server Password Unlocker, click Open, import the master.mdf file. All the user names of your SQL Server will be displayed.
Step 3. Select an SQL account, such as 'SA', click Change Password button.
Step 4. Type a new password, and then click OK.
The new password has been set for your SA account, now you can login SQL Server with the new password.
Currently SQL Server 2008 still supports two types validation and authentication for access link to resources database, Windows authentication mode and SQL Server and Windows authentication mode which also known as mixed mode. Both of these authentication methods provide access to SQL Server 2008 and its resources.
The Mixed Mode - SQL Server and Windows Authentication Mode
SQL Server and Windows Authentication Mode uses either Active Directory user accounts or SQL Server accounts when validating access to SQL Server. SQL Server 2005 introduces a new way, the policy of the password and account lockout the policy session of SQL Server by using SQL Server implement authentication. SQL Server 2008 also to do so. The policy of SQL Server that can be used include password complexity, password expiration and account lockout. This feature is not available in SQL Server 2000 and becomes a major security risk for most organizations and database administrators. In essence, this weakness has contributed to the Windows authentication set as best practice for the management of authentication in the past. Today, SQL Server and Windows authentication mode may be able to successfully compete with the Windows Authentication Mode.