I like to say that I am SQL Server Performance Tuning expert but often during the conversation, I end up discussing security as well. Recently I was asked about my preference for Windows Authentication or System Admin Account (SA) during one of the recent Comprehensive Database Performance Health Check.
My opinion about this subject is very much clear – try to use Windows Authentication as much as possible. Usage of mixed mode authentication is recommended for legacy applications and non-Windows users. I recommend this because when multiple users use the same login, it becomes difficult to know which “NT User” actually did an operation on the server. I recommend this for SQL Server Administration too, instead of using the default SYSADMIN SA account. Try not to share the SA account across multiple users.
We can change the authentication type on the Server Properties > Security > Server Authentication; this setting can be found as shown in the above figure.
You can also disable the SA login inside SQL Server. This is because the user account can be easily guessed by anyone working with SQL Server. Ideally, rename or disable this account and use some other account to administer your SQL Server instance. The following command can help you DISABLE / ENABLE the SA user account.
/* Disable SA Login */ ALTER LOGIN [sa] DISABLE GO
/* Enable SA Login */ ALTER LOGIN [sa] ENABLE GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
