Determining Security Settings using TSQL?

Question

Post reply

Determining Security Settings using TSQL?

Ed Mlynar

Mr or Mrs. 500

Points: 593
More actions
July 27, 2009 at 9:24 am

#134329

Is there a way to determine the Authentication mode (SQL Server/Windows Authentication/Both) and Audit Level (None/Failure/Success) using TSQL?
I have a large and varied environment (2000, 2005, and 2008) and am trying to create an audit script to determine which servers we need to look at.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Shawn Melton SSC-Insane Points: 24675 More actions · Answer 1

This SSC post should point you in the right directory:

How can I set the authentication mode for a SQLServer from TSQL

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 2

I would love to know a better way to do this..

But I have done this by using xp_instance_regread and reading the AuditLevel and the LoginMode reg entry. On my express machine it works out to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS2008\MSSQLServer

I am not sure what the more relative path using xp_instance_regread is..

CEWII

Ed Mlynar Mr or Mrs. 500 Points: 593 More actions · Answer 3

I saw that one, but was hoping for a better method.

For SQL 2000 the registry name is HKLM\SOFTWARE\Microsoft\MSSQLServer\{instance}\AuditLevel and LoginMode.

For 2005 and 2008 the registry name is HKLM\SOFTWARE\GUESSWHEREWEAREHIDING\THEVALUEYOUWANT\TODAY\NOT\HERE

It's under \Microsoft SQL Server\MSSQL.n\MSSQLServer where n is a number dependent on the order you installed things, how many things there are, and the foot size of the Microsoft Support guy. If I knew how to determine the n through a query, this method might work.

I just wish Microsoft had created a master..xp_givemethedataiwant stored procedure.

Shawn Melton SSC-Insane Points: 24675 More actions · Answer 4

Well I guess microsoft is sticking to what it knows best, "Keep the people that use/support your product, confused."

I did come across this article on SSC. He has some good notes on the registry keys.

Get And Set SQL Server 2005 Instance Registry Values[/url]

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 5

Instead of trying to figure out how to do this from T-SQL, I would look at using SMO and Powershell to build a script that connects to every server\instance I want to check. Once connected, you can easily get that information from the instance object.

Using SQL Server 2008 client tools - start Powershell using sqlps.exe. If you don't have those client tools, you can download the SQL Server 2008 plug-in for Powershell from Microsoft:

PS> CD SQLSERVER:\SQL\{server name}

PS> Get-ChildItem

The above will list the instance on the server you connected to. Now, all you need to do is:

PS> Get-ChildItem | Select InstanceName, AuditLevel, LoginMode

You can easily create a script that loops through all of the servers in a list and performs the above actions.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs