How to change authentication mode in SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

How to change authentication mode in SQL Server

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 11:04:43

[code]CREATE PROCEDURE dbo.uspSetSQLServerAuthenticationMode
(
@MixedMode BIT
)
AS

SET NOCOUNT ON

DECLARE @InstanceName NVARCHAR(1000),
@Key NVARCHAR(4000),
@NewLoginMode INT,
@OldLoginMode INT

EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
N'MSSQLSERVER',
@InstanceName OUTPUT

IF @@ERROR <> 0 OR @InstanceName IS NULL
BEGIN
RAISERROR('Could not read SQL Server instance name.', 18, 1)
RETURN -100
END

SET @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'

EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
@Key,
N'LoginMode',
@OldLoginMode OUTPUT

IF @@ERROR <> 0
BEGIN
RAISERROR('Could not read login mode for SQL Server instance %s.', 18, 1, @InstanceName)
RETURN -110
END

IF @MixedMode IS NULL
BEGIN
RAISERROR('No change to authentication mode was made. Login mode is %d.', 10, 1, @OldLoginMode)
RETURN -120
END

IF @MixedMode = 1
SET @NewLoginMode = 2
ELSE
SET @NewLoginMode = 1

EXEC master..xp_regwrite N'HKEY_LOCAL_MACHINE',
@Key,
N'LoginMode',
'REG_DWORD',
@NewLoginMode

IF @@ERROR <> 0
BEGIN
RAISERROR('Could not write login mode %d for SQL Server instance %s. Login mode is %d', 18, 1, @NewLoginMode, @InstanceName, @OldLoginMode)
RETURN -130
END

RAISERROR('Login mode is now %d for SQL Server instance %s. Login mode was %d before.', 10, 1, @NewLoginMode, @InstanceName, @OldLoginMode)
RETURN 0[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources