Declare @insname varchar(25)
if convert(varchar(25),serverproperty('instancename')) is null
set @insname='MSSQLSERVER'
else
set @insname=convert(varchar(25),serverproperty('instancename'))
select host_name() as 'HostName',@insname as 'Instance',
serverproperty('productversion') as 'Ver', serverproperty('productlevel') as 'Level',
serverproperty('edition') as 'Edition'
if @insname<>'MSSQLSERVER'
begin
select 'THIS IS BAD! You must reinstall MSSQL' as 'MSSQL is a named instance!'
end
-- If > 2000 check the instance registry path and if remote connections
-- are enabled.
if serverproperty('productversion')>'9'
begin
Declare @inspath varchar(10),@reg varchar(100),@tcpremote as int
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
@value_name=@insname,
@value=@inspath OUTPUT
set @reg='Software\Microsoft\Microsoft SQL Server\' + @inspath + '\MSSQLSERVER\SuperSocketNetLib\tcp\'
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg,
@value_name='Enabled',
@value=@tcpremote OUTPUT
select @inspath as 'Instance path',@tcpremote as 'Remote connections enabled?'
if @tcpremote='0'
begin
EXEC master.dbo.xp_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg, @value_name='Enabled', @type='REG_DWORD',@value=1
select '! Please reboot !' as 'I had to enable remote connections'
end
else
begin
select 'This is good!' as 'Remote connections already enabled'
end
end
else
begin
select 'Nothing else to check' as 'MSSQL version < 2005'
end
)
