Secure and disable the SQL Server SA Account

Problem

Ideally your SQL instance would be configured to only allow for Windows Authentication. There may be times when mixed mode authentication is necessary at which point you will should configure a method to rotate the ‘sa’ password on a regular basis. You want the new password to be random and secure from others. Not only do you not want anyone else to know the password, you don’t even want to know it yourself.

Solution

SQL Server has an undocumented system stored procedure named sp_SetAutoSAPasswordAndDisable. This procedure will do exactly as the name suggests: it will reset the password and then disable the 'sa' login.

---

The procedure takes no parameters, so the syntax for usage is as follows:

EXEC sp_SetAutoSAPasswordAndDisable
GO

After completion you should see the standard message:

Command(s) completed successfully. 

The actual code is as follows:

ALTER procedure [sys].[sp_SetAutoSAPasswordAndDisable]
as
    -- can execute only as SysAdmin
 if (not (is_srvrolemember('sysadmin') = 1))  -- Make sure that it is the SA executing this.
 begin
  raiserror(15247,-1,-1)
  return(1)
 end
    -- Begin a transaction
    BEGIN TRANSACTION
    
 -- Disable Password Policy on the SA Login
 ALTER LOGIN sa WITH CHECK_POLICY = OFF
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END
 -- Create a New Guid as the random password
 declare @randompwd UNIQUEIDENTIFIER
 declare @stmt nvarchar(4000)
 SET @randompwd = newid()
 SELECT @stmt = 'ALTER LOGIN sa WITH PASSWORD = ' + quotename(@randompwd, '''')
 EXEC(@stmt)
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END
 -- Now set the policy back
 ALTER LOGIN sa WITH CHECK_POLICY = ON
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END 
 -- Now set the policy back
 ALTER LOGIN sa DISABLE
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END 
 -- Commit the transaction
 COMMIT TRANSACTION

When you execute this stored procedure the password for the ‘sa’ login will be reset to a random GUID, and then be disabled. Auditors love this aspect because not only is the password secure, but so is the account itself.

If you need to roll your own solution to rotate the password for the ‘sa’ login, then the sp_SetAutoSAPasswordAndDisable stored procedure may be exactly what you are looking for.

Source Collected from MSQLTIPS.COM