The sa account is a well, known, built in account for SQL Server. Years ago, in previous

versions, I’d see people often use the “sa” account for development, usually with a blank password. Even those speakers and experts often showed demo code with a blank “sa” password.

Ugh, a horrible example that was repeated over and over until the SQL Server setup program stopped allowing blank password. However people then had “12345” or other simple things to type on stage. I guess some habits never change.

In any case, I saw someone ask recently about changing the “sa” password in response to auditors’ requests. This person asked about how to randomly set this on a regular basis.

It’s easy. Even if you have Windows Auth only, you can use this script.

DECLARE @pwd UNIQUEIDENTIFIER = NEWID()
, @new VARCHAR(50);

SELECT @new = CAST(@pwd AS VARCHAR(50))

EXEC sp_password @new = @new, @loginame = ‘sa’

That seems to work fine in testing. I get a new sa password each time, whether I have Windows Auth or Mixed mode set. If I change the password with only Windows Auth, the last password set works if I change to mixed mode.

This is a great little script to set up in a job and run it monthly. This will protect your sa account in case someone ever enables mixed mode.

If you need the account, just change the password then, for the job/application that needs to run. Then run your job again to reset it.

Filed under: Blog Tagged: security, sql server, syndicated