authentication - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

authentication

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-06-18 : 11:32:22

hi,
What is the sql syntax to get the sql server authentication level please?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 13:20:05

What do you mean?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-06-18 : 18:04:23

You mean NT Auth, SQL AUth or Mixed mode?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-06-19 : 03:51:10

yes, I would like to know which authentication level the server is set to.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-19 : 13:24:28

Use xp_regread to read the info from the registry.

Here is where it is stored on mine:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

LoginMode is the string value. 1 = Windows Auth, 2 = mixed mode


DECLARE @path int

EXEC master.dbo.xp_regread 
	N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER',
	N'LoginMode', 
	@path output

PRINT @path

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-19 : 13:32:25

The MSSQL.1 info could vary if you've got multiple instances on your machine.

Use this to find it programmatically: http://weblogs.sqlteam.com/tarad/archive/2006/06/07/10114.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-19 : 13:41:28

Or an easier way: EXEC master.dbo.xp_loginconfig 'login mode'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources