InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 00:29:12 GMT20http://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxSorry to dig up an old thread, but this thread was returned by Google when I was looking at getting the same information, and I have something to add to assist anyone else stumbling across this tread looking to read the AuditLevel via script.Use xp_instance_regread instead of xp_regread when looking for info in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer branch of the registry, it will sub in the correct instance syntax.DECLARE @AuditLevel intEXEC master..xp_instance_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', @value_name='AuditLevel', @value=@AuditLevel OUTPUTSELECT @AuditLevelThu, 22 Mar 2012 19:49:25 GMTJason Lhttp://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxSSMS is SQL Server Management Studio. That's correct.I think the confusion is that SSMS should be the source for verifying how things are set. Not necessarily the registry as you might not get the correct key matched to the correct setting.Thu, 16 Apr 2009 12:23:28 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxThanks for all those who tried to help me out.I guess we are going away from the topic. I have SQL Server Management Studio on all of the servers and I'm using it to administer the DB's. I guess you are referring Mangement Studio as SSMS? Is that right?I will keep you posted once I see any difference with reg entries / sql server logs once I change it to 'Successful and Failed Logins'. Still I am not able to understand my current settings.Thanks,Philip.Thu, 16 Apr 2009 11:39:20 GMTPhilip-918225http://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxSSMS is not a free tool. It's the tool built by MS and designed to connect to and manage SQL Server. It's installed as part of the client tools, and may or may not be set up on your server.However, when you connect to an instance with SSMS, it reads data from the system tables, the file system, and the registry on that server to determine how SQL Server is being run or is configured.You should be using SSMS to administer your servers, not the registry.Thu, 16 Apr 2009 11:18:41 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxThere was a confusion regarding SSMS (I didn't know that it is a reg reading tool) as I thought it is nothing but the Sql Server Management Studio (SSMS?).As SSMS is a free tool I'm not allowed to run it on the server. That's why I have gone into regedit and searched for AuditLevel.I guess only option I have now is to change the Audit option and see how it records details in the SQL Server Logs. Would you agree with it?Thanks,Philip.Thu, 16 Apr 2009 11:14:44 GMTPhilip-918225http://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxI can't explain the registry, and honestly you're not supposed to muck in there. The SSMS tool reads the registry, but it may be reading a different place than you're looking and that would be the confusion. The reading in SSMS is what you should go on. there's probably a corresponding server property to this that you can read from T-SQL as well.The databases don't have a security tab since they are dealing with users, not logins.Thu, 16 Apr 2009 10:39:45 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxI think we are talking about the same thing. When I connect to the DB using Mangement Studio, I get ServerName (SQL Server 9.0.3077 - Domain\username) and I write click and the security tab says 'Failed Logins Only' this is what I referred in my earlier post. The individual databases under the Database tab which comes under the Server name hasn't got a security tab under properties.My concern here is why it is having 3 in Registry and Failed Logins only (2) in SSMS. How can this be possible?Thanks,Philip.Thu, 16 Apr 2009 10:32:19 GMTPhilip-918225http://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxThere's always a security tab for 2005 instances. You select the server instance, right click, properties. The dialog that appears has choices on the left. Security is one of those.Thu, 16 Apr 2009 10:19:01 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxI have a number of DB's on the server and I thought the AuditLevel is for the entire DB Server as there isn't any Security Tab if you look at the properties of the individual instances.under HKEY_LOCAL_MACHINE I have performed a serach for AuditLevel and it returned only 2 rows,1. (Default) REG_SZ MSSQLSERVER2. (AuditLevel) REG_DWORD 0x00000003 (3)Thanks,PhilipThu, 16 Apr 2009 10:15:53 GMTPhilip-918225http://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxThe key is dependent on which instance you are looking at. SSMS should read it from the registry, so I'm thinking that you're looking at the wrong key. Whatever is in SSMS is what should be set. Change it there.Thu, 16 Apr 2009 10:05:03 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxI have initially checked it via SSMS and it is having the Login Auditing option as 'Failed Logins Only'. Then I have checked the Reg key AuditLogin and saw it as 3.Later I found the following SQL from this site,----------------------------------------------------------DECLARE @AuditLevel intEXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', @value_name='AuditLevel', @value=@AuditLevel OUTPUT SELECT @AuditLevel----------------------------------------------------------which returned NULL. I have then checked the SQL Server Logs and couldn't see any login audits there (this might be due to the fact that there wasn't any failed login attempts!)Thanks,PhilipThu, 16 Apr 2009 10:02:24 GMTPhilip-918225http://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxDo you have multiple instances on the box? If so, are you looking at the registry keys for the right instance?Thu, 16 Apr 2009 09:58:11 GMTSA-1http://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxHow are you checking this? The error log?What is the setting in SSMS? All logins?Thu, 16 Apr 2009 09:52:49 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic698590-146-1.aspxHi All,I have a SQL Server 2005 Server with AuditLevel 3 (0x00000003) as per the registry key AuditLevel. When I checked it through the Management Studio I'm seeing it recording only faied logins only which means that it should have been 2.Audit Level Registry Value None 0x00000000 (0) Success 0x00000001 (1) Failure 0x00000002 (2) All 0x00000003 (3) Then I tried to determine it through T-SQL,I got the result as NULL.I would like to cofirm that I am on AuditLevel 3. How can I confirm this?Thanks,PhilipThu, 16 Apr 2009 09:29:05 GMTPhilip-918225