Recently I installed SQL Server 08 R2 Express.
As part of the standard installation the sa login is disabled as default.
Clearly although this is a useful security feature it is a bit awkward if you are wanting to undertake certain tasks and as part of the installation this security feature is in no way obvious.
So what do you do to re-enable it?
Firstly log into SSMS under the machine windows login.
Go to the Security section of the server (not any of the databases) and expand the Logins branch. SA the system admin should be listed and if it is disabled it will have a small red down arrow next to it.
To re-enable highlight sa and right click
Select properties
Select Status
now alter the following
Permissions to connect to database engine
Set this to GRANT
Login
Set this to Enabled
Now highlight instance itself and right click and go to properties.
Within server properties highlight Security.
And change set server authentication to
SQL Server and Windows Authentication mode.
You should have sa login now enabled – don’t worry if sa still has a small red arrow next to it the SSMS client needs to be refreshed to see changes to set up.
If you want to change the password for the SSMS server I would recommend running the following.
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'DifficultPass9£' ;
GO
Note you can enforce the requirement for a strong password within the same security section of the sa login if you require.