Server - SSMS

Change Server Authentication Mode

SQL Server Management Studio

  1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  2. Click on Security and go to the Security page, under Server authentication, select the new server authentication mode, and then click OK.
  3. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
  4. Click on Status and go to the Status page, in the Login section, click Enabled, and then click OK.


Transact-SQL

  1. In Object Explorer, connect to an instance of Database Engine.
  2. Right-click the server, click New Query.
  3. Copy and paste one of the following examples into the query window and click Execute.


The following example enables the sa login and sets a new password.
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '' ;
GO


The following example changes Server Authentication from mixed mode (Windows + SQL) to Windows only.
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', REG_DWORD, 1
GO
View Details

Error 15151

  1. Open SQL Server Configuration Manager.
  2. Double click SQL Server Services.
  3. Right Click SQL Server and select Stop.
  4. Right Click SQL Server and select Properties.
  5. Select Advanced.
  6. Add -m; in front the Startup Parameters.
  7. Right Click SQL Server and select Start.
  8. Open cmd.
  9. Type sqlcmd.
  10. Type CREATE LOGIN recovery WITH PASSWORD = 'password'.
  11. Type sp_addsrvrolemember 'recovery', 'sysadmin'.
  12. Right Click SQL Server and select Stop.
  13. Right Click SQL Server and select Properties.
  14. Select Advanced.
  15. Remove -m; in front the Startup Parameters.
  16. Right Click SQL Server and select Start.
  17. Login your database via Windows Authentication.
  18. Go To Security > Logins > sa.
  19. Insert the new password in password column and confirm password column.
  20. Make sure enforce password policy checked.
  21. Restart database.
View Details

Error 18456

  1. Login your database via Windows Authentication.
  2. Go To Security > Logins > sa.
  3. Insert the new password in password column and confirm password column.
  4. Make sure enforce password policy checked.
  5. Restart database.
View Details

Error 18461

  1. Open SQL Server Configuration Manager.
  2. Double click SQL Server Services.
  3. Right Click SQL Server and select Stop.
  4. Right Click SQL Server and select Properties.
  5. Select Advanced.
  6. Remove -m; in front the Startup Parameters.
  7. Right Click SQL Server and select Start.
  8. Login your database via Windows Authentication.
  9. Go To Security > Logins > sa.
  10. Insert the new password in password column and confirm password column.
  11. Make sure enforce password policy checked.
  12. Restart database.
View Details