T-SQL to Get Default Audit Level of SQL server Instance

I have come across situations where you need to go and look for Default Audit Level for Installed Instances or servers. You can do this by getting info in server instances. Now what if you have around 100 server and instance. For this I could find a easy way and that’s a T-SQL function to get the default Audit Level for Instance.

Function:

USE YourDatabaseName
GO
CREATE FUNCTION dbo.fn_get_default_path()
RETURNS int
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @path int, @value_name nvarchar(20);

    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');

    -- sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';

    SET @value_name = N'AuditLevel'
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;   
    RETURN @path;
END
GO

Now to get the value run the below script:

DECLARE @Temp varchar(max),@Temp1 varchar(max)
SET @Temp = dbo.fn_get_default_path()
If @Temp = 0
Begin
Set @Temp1 = 'NONE'
END
IF @Temp = 1
BEGIN
SET @Temp1 = 'Successful Logins Only'
END
If @Temp = 2
BEGIN
SET @Temp1 = 'Failed Logins Only'
END
IF @Temp = 3
BEGIN
SET @Temp1 = 'Both Successful and Failed Logins'
END
SELECT @Temp1
GO

Technorati Tags: T-SQL,Audit Level,Default Audit Level