SQL Server security best practice

Security! This is the word comes in mind of every concerned person when it come...

Change the Collation Settings in MS SQL Server

This post will show you how to change the collation settings in MS SQL Server for specific database...

Resolve collation conflict

In this post I will show you how you can resolve collation conflict error...

Book: SQL Server 2008 High Availability

In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...

Why to recompile Stored Procedure

Generally, we create views and stored procedures (proc here after) ...


Change Authentication Mode with Registry

What if we need to change / read / write some registry entry in Server but we donot have access to server!!!! but at the same time we do have SA/Admin privilege on SQL Server (Assuming we need to change registry settings on SQL Server itself ) for example while setting up SQL Server they have installed SQL Server with *Mixed Authentication Mode* and now we need to change it to *Windows Only Authentication Mode* to do so from Query Analyzer we do have xTended Store Procedure to help us they are xp_regread and xp_regwrite
For an instance assume that your Authentication Mode is set to *Mixed Authentication Mode* to read it run below query in QA if it shows ‘0’ or '2' means that authentication mode is Mixed

EXECUTE master..xp_regread

Now if we wants to change it to *Windows Only* run below query in QA

declare @md int

set @md=1

EXECUTE master..xp_regwrite



'LoginMode', 'reg_dword', @md

This will make changes in your Registry and change your *Authentication Mode* also.

Disclaimer : In the example above both the xTended Stored Procedure used are Undocumented so use it at your own risk and please check for its availability / support before using it.

This posting is provided * AS IS * for the shake of knowledge sharing only.




Hemantgiri S. Goswami is SQL Server Specialist and Consultant, he is former SQL Server MVP. Hemantgiri is contributing to the SQL Server Community by participating in various SQL Server forums, authoring articles at:

Hemantgiri is founder and leader of the Surat User Group which primarily focusing on SQL Server and Windows OSs.

Hemantgiri S. Goswami Blogs at SQL Server Citation recent he has started writing on SQL Server in his native language Gujarati visit SQL Server Citation - Gujarati . Hemantgiri was awarded Most Valuable Professional (MVP) award by Microsoft for his contribution to the SQL Server Community.