It has been a full two weeks, actually more, since my last post. You may recall my last entry dealt with my inability to find quality DBA's after months of searching. I am happy to report (and recant part of my frustrated recitation on not finding anyone) that I now have two very solid DBAs on my team....but that is not the subject tonight.
Tonight, I wanted to post a quick little query for those who shun best practices. I am sorry, I seem to be doing this much lately. I should say, really, I adhere to security manifestos as part of my job and I eschew, like other DBAs, any security privilege that is unnecessary in practice. That being said, here is a query that I use to ascertain the service accounts used to fire up and run any SQL Service on any server. It uses WMIC (Windows Management Instrumentation Console) and xp_cmdshell, both of which require special privileges to run. However, assuming the service account for SQL Server has administrator pivileges or is Local System, the script should work just fine. When dealing with 40+ SQL Servers and requirments to change known passwords (in an imperfectly secured glass box world) the following script can be a cornerstone of a plan to get the ball rolling in that direction. The few things worth noting in this simple script is the pipe to findstr "grepping" SQL and the use of the charindex function to parse the service name and service account. Give it a shot IF you have admin privileges with your account or IF you have your SQL Server logon credentials for the service with admin privileges (not advised as best practice and defintely not going to garner me any Microsoft MVP nominations). One server? Yeah do the work manually...two?Sure...same...20+ servers?...IF THEN ELSE.
If
exists ( Select Name from tempdb..sysobjects where name like '#MyTempTable%' )
Drop Table #MyTempTable
Create
Table #MyTempTable
(
Big_String
nvarchar(1000)
)
Insert
Into #MyTempTable
EXEC master..xp_cmdshell 'WMIC SERVICE GET caption,StartName | findstr /I "SQL"'
Select
@@ServerName as ServerName,
Rtrim(Left(Big_String, charindex(' ', Big_String))) as Service_Name,
RTrim(LTrim(Rtrim(Substring(Big_String, charindex(' ', Big_String),
len(Big_String))))) as Service_Account
from
#MyTempTable
where
Big_String is not null