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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy