Post reply

xp_instance_regread redirects

  • September 29, 2010 at 5:20 pm

    #387898

    Found something interesting.

    I have two instances on the same server. One is the default instance and one is a named instance. Neither one of them is located in registry path HKEY_LOCAL_MACHINE:Software\Microsoft\MSSQLServer\MSSQLServer

    Depending on which instance I run this query from, it redirects behind the scenes to look in the proper registry location to get the auditing value for that instance.

    HKEY_LOCAL_MACHINE:Software\Microsoft\MSSQLServer\MSSQLServer does exist but it is not where the audit level value is stored for either instance.

    Just thought that was interesting that it redirects and could be useful to someone.

    It works in SQL Server 2000, 2005, and 20008 so I am using this query on the Central Management Server to confirm that auditing is enabled for each instance.

    IF OBJECT_ID('tempdb..#tempAuditLevel') IS NOT NULL

    BEGIN

    DROP TABLE #tempAuditLevel

    END

    create table #tempAuditLevel(Value varchar(10), [Audit Level] varchar(1))

    INSERT INTO #tempAuditLevel

    EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel'

    select @@SERVERNAME as [Instance]

    ,case when [Audit Level] = '3' then 'Both Failed and Successful Logins'

    when [Audit Level] = '2' then 'Failed Logins Only'

    when [Audit Level] = '1' then 'Successful Logins Only'

    when [Audit Level] = '0' then 'No Login Auditing'

    else [Audit Level] end as [Audit Level]

    from #tempAuditLevel

    Howard

  • October 5, 2010 at 6:32 am

    #1231626

    Nicely documented. Thank you Howard.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 2 posts - 1 through 1 (of 1 total)

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