How to get 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 get authentication mode in SQL Server

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 11:08:30

[code]CREATE FUNCTION dbo.fnGetSQLServerAuthenticationMode
(
)
RETURNS INT
AS

BEGIN
DECLARE @InstanceName NVARCHAR(1000),
@Key NVARCHAR(4000),
@LoginMode 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
RETURN NULL

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

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

RETURN @LoginMode
END[/code]

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-19 : 11:46:15

this also works:
exec xp_loginconfig 'login mode'

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 11:52:51

Great!

Mladen, do you know if the security breach has been fixed?
For some time ago, I could set DefaultLogin to SA and then I got sa security rights for any account I logged into sql server.
login mode had to be integrated.

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-19 : 12:01:10

you could set it via xp_loginconfig?

never heard of that bug

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 14:02:06

No, via the registry.
The caveat was that SQL Server service needed to restart first.

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