Query The start up type of a service

Question

Query The start up type of a service

SQLAssAS

SSCertifiable

Points: 7246
More actions
July 16, 2013 at 8:53 am

#295554

I am just putting together a list of security checks which will eventually have a front end to them for audit purposes.
Does anybody know a script which will return the start up types of the services? SQL, Agent, Browser? I want to ensure that SQL is automatic and browser is disabled on each instance.
Any help is much appreciated

Viewing 5 posts - 1 through 4 (of 4 total)

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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

I think a Powershell script could easaly return the information you want. I don't have such script at hand, but take a look at the Technet Scriptcenter http://gallery.technet.microsoft.com/ScriptCenter/ for many examples.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 2

CHeers - I do have a powershell script but I have no idea if its possible or how to insert this into a SQL table.

I want to input the results into a SQL table I have created for all the Checks i will be making.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

Here's how you can do it without PowerShell. Note that, in the code below, I am only getting the information for the SQL Service Account. The steps will be similar for the other accounts:

-- Script to read the registry for service account details

USE tempdb;

IF OBJECT_ID('tempdb..serviceaccountinfo') IS NOT NULL

DROP TABLE serviceaccountinfo;

CREATE TABLE serviceaccountinfo

(id int identity primary key,

Instance varchar(100) not null,

[Service] varchar(100) not null,

Startup varchar(20) not null);

DECLARE @sql_acct varchar(100)=@@SERVICENAME; -- put your instance name here

DECLARE @rootBase varchar(100)='SYSTEM\ControlSet001\services\MSSQL$',

@instance varchar(200),

@startupType int;

DECLARE@buildkey varchar(100)=@rootBase+@sql_acct;

EXEC xp_regread @root_key = 'HKEY_LOCAL_MACHINE'

, @key = @buildkey

, @valuename = 'DisplayName'

, @value = @instance output;

EXEC xp_regread @root_key = 'HKEY_LOCAL_MACHINE'

, @key = @buildkey

, @valuename = 'Start'

, @value = @startupType output;

--Results

INSERT INTO tempdb..serviceaccountinfo

SELECT@@SERVERNAME AS Instance,

@instance AS [Service],

StartUp = CASE @startupType

WHEN 4 THEN 'Disabled'

WHEN 3 THEN 'Manual'

WHEN 2 THEN 'Automatic'

ELSE 'Unknown' END

SELECT * FROM tempdb..serviceaccountinfo;

--DROP TABLE tempdb..serviceaccountinfo;

GO

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 4

Alan,

Thanks for the code, works a treat and as you say, its easy to modify for the other services other than just the instance.