Rarely, one needs to change the SQL Server configuration. However, when such a change is required, it needs to be planned and scheduled for execution during an outage. There too, the team implementing the change may or may not be the same as those recommending the change. In such cases, it becomes essential to script the changes made so that the change can be supplied to the implementation team for:

1. Record keeping – for future reference
2. Scheduled deployment – the required change has been scripted, so it can be applied whenever the outage window permits

Generally, configuration changes to the SQL Server are made via the use of the system stored procedure – sp_configure. However that is not true for all situations. For example, if a SQL Server needs to be configured to use Windows authentication only, this setting cannot be made via sp_configure, making it difficult to script. Today, I will show you one such method available within SSMS that allows you to script such “difficult” configuration changes.

NOTE: The methods described in this post are provided “as-is” and without warranty. Please do not use these in production without prior thorough testing. Incorrect use of these methods may result in serious, system-wide, irreversible configuration issues with your server. This site (BeyondRelational.com), the author or Microsoft and it’s affiliates are not responsible for damage resulting due to misuse of these methods. Please use these methods at your own risk.

1. Login to your SQL Server instance using SSMS
2. From the object explorer, right-click on the SQL server instance name to bring up the instance properties window
3. Go to the “Security” tab
4. The server I am connecting to had mixed-mode authentication on. I now want to change the authentication mode to “Windows authentication mode”. Do NOT apply the change
5. 
6. Click on the little arrow near the “script” button at the top
7. 
8. Choose “Script Action to New Query Window” or press Ctrl+Shift+N
9. Notice that a new query window opens up with the underlying change scripted and ready for execution
10. 

11. USE [master]
    GO
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'LoginMode', REG_DWORD, 1
    GO

12. The change can now be executed during the regular maintenance window for my instance

Until we meet next time,

Be courteous. Drive responsibly.