SQL Server Brain Basher of the Week #028 - Authentication

Microsoft SQL Server supports two type of authentication modes; Windows authentication and Mixed authentication. Windows authentication is always recommended unless some applications require SQL Server logins for connecting with SQL Server instance. Here is the Brain Basher of the week based on it.

If Windows authentication is selected during the installation, and later authentication is enabled to Mixed authentication, what step(s) you have to take for using sa login?

1. No additional steps required. sa login can be used immediately with a blank password.
2. Enable sa login either using ALTER LOGIN statement or Login Properties GUI for sa.
3. Reset the system assigned password of sa either using ALTER LOGIN statement or Login Properties GUI for sa.
4. Assign sa login to all databases either using CREATE USER statement or Login Properties GUI for sa.

If Windows authentication is selected during the installation, sa login is still created with a system password and kept as a disabled SQL login. If you need to use sa login after enabling to Mixed authentication, you need to perform step (2) and step (3).

For more info: https://msdn.microsoft.com/en-us/library/ms188670.aspx