During the installation of SQL Server, you are prompted to select an authentication mode.
Regardless of the choice, a [sa] account will be created. If you are using Windows Authentication, the account will be be left in a disabled state. On the other hand, if you are using Mixed Mode Authentication, you are asked for a password and the [sa] account will be left in an enabled state.
Because the sa account is well known and often targeted by malicious users, do not enable the [sa] account unless your application requires it. Never set a blank or weak password for this account.
Some organizations try to protect the Local Windows Administrator account or the SQL server admin account by obscuring the name of the account. Below is a TSQL snippet that will disable and rename the [sa] account.
1 2 3 4 5 |
-- Disable the account ALTER LOGIN [sa] DISABLE; -- Rename the account ALTER LOGIN [sa] WITH NAME = [sys-adm]; |
Keep in mind that this action might causes issues with software that is hard coded. For instance, the following support ticket at Microsoft shows that the upgrade script from SQL Server 2005 to 2008 fails due to this issue.
Instead, Microsoft should have searched by looking for the SID instead of the NAME. The TSQL snippet below searches for the sa account by SID.
1 2 3 4 |
-- Search for the account SELECT p.name FROM sys.server_principals P WHERE P.type = 'S' AND P.name = SUSER_SNAME(0x01) |
In short, unless you need to use the [sa] account, leave it in a disabled state.