Archive for the ‘SQL Server Services Accounts’ Category

How to query the Windows Registry and display the SQL Server services accounts?

You wish to find out the accounts that are being used as the startup accounts for the SQL Server service and SQL Server Agent service. Rather than having to RDP into the server and then opening the SQL Server Configuration Manager to view all the “Log On As” accounts, is there an easier way? Yes there is. You can query the Windows Registry by using the XP_REGREAD stored proc. Here’s the code to get you more than what you need:

IF OBJECT_ID('TEMPDB..#SQLServerServiceAccounts') IS NOT NULL
DROP TABLE #SQLServerServiceAccounts


/*============================================
sql server services credentials.
identify the services credentials for the sql server service, sql server agent service, sql server analysis service, 
sql server integration service, sql server reporting service, sql server fulltext search service, sql server browser service, 
sql server active directory helper, sql server vss writer.
  ============================================*/  
--set nocount on 
if object_id('tempdb..#servicecredentials') is not null
	drop table #servicecredentials;
if object_id('tempdb..#services') is not null
	drop table #services;
if object_id('tempdb..#getinstances') is not null
	drop table #getinstances;
if object_id('tempdb..#tchkkey') is not null 
	drop table #tchkkey

create table #tchkkey (keyexist int)
	
create table #servicecredentials
	([servername] nvarchar(255)
	,[servicename] nvarchar(255)
	,[serviceaccount] nvarchar(255))

create table #services
	(serviceid int identity(1,1)
	,servicename nvarchar(255))
	
create table #getinstances
	(instanceid int identity(1,1)
	,value nvarchar(100)
	,instancenames nvarchar(100)
	,data nvarchar(100))

-- list of sql server services in registry
insert into #services (servicename) values  ('mssql$')
insert into #services (servicename) values  ('mssqlserver')
insert into #services (servicename) values  ('sqlagent$')
insert into #services (servicename) values  ('sqlserveragent')
insert into #services (servicename) values  ('msolap$')
insert into #services (servicename) values  ('mssqlserverolapservice')
insert into #services (servicename) values  ('reportserver$')
insert into #services (servicename) values  ('reportserver')
insert into #services (servicename) values  ('msdtsserver')
insert into #services (servicename) values  ('msdtsserver100')
insert into #services (servicename) values  ('msftesql$')
insert into #services (servicename) values  ('sqlbrowser')
insert into #services (servicename) values  ('mssqlserveradhelper')
insert into #services (servicename) values  ('mssqlserveradhelper100')
insert into #services (servicename) values  ('sqlwriter')
insert into #services (servicename) values  ('msftesql$')
insert into #services (servicename) values  ('msftesql')
insert into #services (servicename) values  ('mssqlfdlauncher$')
insert into #services (servicename) values  ('mssqlfdlauncher')

insert into #getinstances (value, instancenames, data)
execute xp_regread
  @rootkey = 'hkey_local_machine',
  @key = 'software\microsoft\microsoft sql server',
  @value_name = 'installedinstances'

declare  @servicename varchar(255)
		,@serviceinsname varchar(255)
		,@serviceaccountname varchar(255) 
		,@key_value varchar(100)
		,@serviceid int
		,@instanceid int
		,@instancename varchar(100)
set @serviceid = 0
set @instanceid = 0
------------------------------------
while exists (select 1 from #services where serviceid > @serviceid)
begin
	select top 1 @serviceid = s.serviceid, @servicename = s.servicename from #services s where s.serviceid > @serviceid order by s.serviceid asc

	if @servicename like '%$%' 
	begin 
		while exists (select 1 from #getinstances where instanceid > @instanceid)
		begin
			select top 1 @instanceid = i.instanceid, @instancename = i.instancenames from #getinstances i where i.instanceid > @instanceid order by i.instanceid asc
			set @serviceinsname = @servicename + @instancename 

			set @key_value = 'system\currentcontrolset\services\' + @serviceinsname

			-- make sure registry branch is existing
			insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
			-- make sure registry branch is existing
			if exists(select * from #tchkkey where keyexist = 1)	
				execute master..xp_regread 'hkey_local_machine', @key_value, 'displayname', @serviceinsname output
			truncate table #tchkkey
		
			-- make sure registry branch is existing
			insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
			-- make sure registry branch is existing
			if exists(select * from #tchkkey where keyexist = 1)
				execute master..xp_regread 'hkey_local_machine', @key_value, 'objectname', @serviceaccountname output
			truncate table #tchkkey

			if @serviceaccountname is not null
			begin
				insert into #servicecredentials
				(servername, servicename, serviceaccount)
				values
				(cast(serverproperty('servername') as nvarchar), @serviceinsname, @serviceaccountname)
				set @serviceaccountname = null
			end
		end
		set @instanceid = 0
	end
	else
	begin
		set @key_value = 'system\currentcontrolset\services\' + @servicename

		-- make sure registry branch is existing
		insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
		-- make sure registry branch is existing
		if exists(select * from #tchkkey where keyexist = 1)
			execute master..xp_regread 'hkey_local_machine', @key_value, 'displayname', @servicename output
		truncate table #tchkkey
		
		-- make sure registry branch is existing
		insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
		-- make sure registry branch is existing
		if exists(select * from #tchkkey where keyexist = 1)
			execute master..xp_regread 'hkey_local_machine', @key_value, 'objectname', @serviceaccountname output
		truncate table #tchkkey
		
		if @serviceaccountname is not null
		begin
			insert into #servicecredentials
			(servername, servicename, serviceaccount)
			values
			(cast(serverproperty('servername') as nvarchar), @servicename, @serviceaccountname)
			set @serviceaccountname = null
		end
	end
end


SELECT sc.servicename AS [ServiceName], 
	sc.serviceaccount AS [ServiceAccount]
FROM #servicecredentials sc

In case you wish to send this out as an html report via email, this is how to do it:

/*============================================
sql server services credentials.
identify the services credentials for the sql server service, sql server agent service, sql server analysis service, 
sql server integration service, sql server reporting service, sql server fulltext search service, sql server browser service, 
sql server active directory helper, sql server vss writer.
  ============================================*/  
 /*NOTE: Replace the recipients email addresses in line #154 */

set nocount on
declare @instance_name varchar(55);
select @instance_name = CONVERT(varchar, SERVERPROPERTY('SERVERNAME'));

IF OBJECT_ID('TEMPDB..#SQLServerServiceAccounts') IS NOT NULL
DROP TABLE #SQLServerServiceAccounts

if object_id('tempdb..#servicecredentials') is not null
	drop table #servicecredentials;
if object_id('tempdb..#services') is not null
	drop table #services;
if object_id('tempdb..#getinstances') is not null
	drop table #getinstances;
if object_id('tempdb..#tchkkey') is not null 
	drop table #tchkkey

create table #tchkkey (keyexist int)
	
create table #servicecredentials
	([servername] nvarchar(255)
	,[servicename] nvarchar(255)
	,[serviceaccount] nvarchar(255))

create table #services
	(serviceid int identity(1,1)
	,servicename nvarchar(255))
	
create table #getinstances
	(instanceid int identity(1,1)
	,value nvarchar(100)
	,instancenames nvarchar(100)
	,data nvarchar(100))

-- list of sql server services in registry
insert into #services (servicename) values  ('mssql$')
insert into #services (servicename) values  ('mssqlserver')
insert into #services (servicename) values  ('sqlagent$')
insert into #services (servicename) values  ('sqlserveragent')
insert into #services (servicename) values  ('msolap$')
insert into #services (servicename) values  ('mssqlserverolapservice')
insert into #services (servicename) values  ('reportserver$')
insert into #services (servicename) values  ('reportserver')
insert into #services (servicename) values  ('msdtsserver')
insert into #services (servicename) values  ('msdtsserver110')
insert into #services (servicename) values  ('msftesql$')
insert into #services (servicename) values  ('sqlbrowser')
insert into #services (servicename) values  ('mssqlserveradhelper')
insert into #services (servicename) values  ('mssqlserveradhelper110')
insert into #services (servicename) values  ('sqlwriter')
insert into #services (servicename) values  ('msftesql$')
insert into #services (servicename) values  ('msftesql')
insert into #services (servicename) values  ('mssqlfdlauncher$')
insert into #services (servicename) values  ('mssqlfdlauncher')

insert into #getinstances (value, instancenames, data)
execute xp_regread
  @rootkey = 'hkey_local_machine',
  @key = 'software\microsoft\microsoft sql server',
  @value_name = 'installedinstances'

declare  @servicename varchar(255)
		,@serviceinsname varchar(255)
		,@serviceaccountname varchar(255) 
		,@key_value varchar(100)
		,@serviceid int
		,@instanceid int
		,@instancename varchar(100)
set @serviceid = 0
set @instanceid = 0
------------------------------------
while exists (select 1 from #services where serviceid > @serviceid)
begin
	select top 1 @serviceid = s.serviceid, @servicename = s.servicename from #services s where s.serviceid > @serviceid order by s.serviceid asc

	if @servicename like '%$%' 
	begin 
		while exists (select 1 from #getinstances where instanceid > @instanceid)
		begin
			select top 1 @instanceid = i.instanceid, @instancename = i.instancenames from #getinstances i where i.instanceid > @instanceid order by i.instanceid asc
			set @serviceinsname = @servicename + @instancename 

			set @key_value = 'system\currentcontrolset\services\' + @serviceinsname

			-- make sure registry branch is existing
			insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
			-- make sure registry branch is existing
			if exists(select * from #tchkkey where keyexist = 1)	
				execute master..xp_regread 'hkey_local_machine', @key_value, 'displayname', @serviceinsname output
			truncate table #tchkkey
		
			-- make sure registry branch is existing
			insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
			-- make sure registry branch is existing
			if exists(select * from #tchkkey where keyexist = 1)
				execute master..xp_regread 'hkey_local_machine', @key_value, 'objectname', @serviceaccountname output
			truncate table #tchkkey

			if @serviceaccountname is not null
			begin
				insert into #servicecredentials
				(servername, servicename, serviceaccount)
				values
				(cast(serverproperty('servername') as nvarchar), @serviceinsname, @serviceaccountname)
				set @serviceaccountname = null
			end
		end
		set @instanceid = 0
	end
	else
	begin
		set @key_value = 'system\currentcontrolset\services\' + @servicename

		-- make sure registry branch is existing
		insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
		-- make sure registry branch is existing
		if exists(select * from #tchkkey where keyexist = 1)
			execute master..xp_regread 'hkey_local_machine', @key_value, 'displayname', @servicename output
		truncate table #tchkkey
		
		-- make sure registry branch is existing
		insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
		-- make sure registry branch is existing
		if exists(select * from #tchkkey where keyexist = 1)
			execute master..xp_regread 'hkey_local_machine', @key_value, 'objectname', @serviceaccountname output
		truncate table #tchkkey
		
		if @serviceaccountname is not null
		begin
			insert into #servicecredentials
			(servername, servicename, serviceaccount)
			values
			(cast(serverproperty('servername') as nvarchar), @servicename, @serviceaccountname)
			set @serviceaccountname = null
		end
	end
end

-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)

SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
	BEGIN
		SELECT @TableHTML = 
		'<html>
		<div style="font-family:Verdana; font-size:14pt;"><div style="font-weight:bold; width:auto; float:left;">'+@instance_name+'</div><div style=" width:auto; float:left; padding-left:5px; "> SQL Server Services Accounts:</div><br></div></div><br>
		<table style="border: 1px solid #000000; border-collapse:collapse; table-layout:fixed; font-family:Arial; font-size:11px;" cellpadding=0 cellspacing=0 border=0>' +
		'<tr style="border: 1px solid #000000; font-family:Verdana; font-size:12px; background-color:#3B8D24; color:white; height:30px;">
			<th style="border: 1px solid #000000;" align=center>Service Name</th>
			<th style="border: 1px solid #000000;" align=center>Service Account</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td style="border: 1px solid #000000;">' + [servicename] + '</td>' +
			'<td style="border: 1px solid #000000;">' + [serviceaccount] + '</td></tr>'FROM #servicecredentials 

		SELECT @TableHTML = @TableHTML +'</table></html>'
			
		
		-- Send email notification
		SELECT @MailSubject = @instance_name + ' SQL Server Services Accounts'
		
		-- Find the database mail profile name
		create table #dbmail_profile
		(
		profileid int null,
		profilename varchar(125) null,
		accountid int null,
		accountname varchar(125) null,
		sequencenumber int null
		)
		insert #dbmail_profile
		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

		select @MailProfile = profilename
		from #dbmail_profile
		where sequencenumber = 1

		drop table #dbmail_profile

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @MailProfile,
			@recipients = @Recipients,
			@subject = @MailSubject,
			@body_format = 'HTML',
			@body = @TableHTML;
	END
set nocount off	

Here’s an example of what it looks like:
SQL Server Instances and Service Accounts