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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s