July 27, 2009 at 9:24 am
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.
July 27, 2009 at 9:49 am
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
July 27, 2009 at 9:52 am
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
July 27, 2009 at 10:07 am
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.
July 27, 2009 at 10:24 am
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
July 27, 2009 at 10:32 am
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply