Rodney

If ONLY (administrator = TRUE) BEGIN...

Published Tuesday, May 22, 2007 9:31 PM

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

 

by Rodney

Comments

 

Haywood said:

I've done similar with PowerShell and a table-list of SQL Servers, except that I use a table to iterate against in the script.  

Apologies if the formatting gets nuked...

[code]
## Get_Service_Info.ps1
##
## 05/11/2007 G. Rayburn <grayburn@collegeloan.com>
##
## This script will query a db to get a list of
## SQL servers on the network.  The table dbo.Server_List
## is populated by a job that runs an sp which executes
## "sqlcmd /Lc" and has logic to only update the table with
## new servers.
##

## Set common connection string:
##
$connString = "server=<ServerName>;database=<DBName>;trusted_connection=true"

## Get a network credential (pref. DomAdmin) for use against the servers:
##
$cred = Get-Credential

## Get a dataset of servers to check from the database:
##
$query = "SELECT SystemName FROM dbo.Server_List ORDER BY SystemName"
$dataset = New-Object "System.Data.DataSet" "MyDataSet"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query, $connString)
$da.Fill($dataset) | out-null
$dataset.Tables[0].TableName = "Server_Info"
$Server_Info = $dataset.Tables["Server_Info"]

## Connection objects for INSERT operations:
##
$sql_conn = New-Object system.Data.SqlClient.SqlConnection
$sql_Conn.connectionstring = $connString
$sql_conn.open()

## Start up the loop and insert into the database:
##
foreach ($row IN $Server_Info.Rows)
{
write-host "Checking: " $row["SystemName"]
write-host

$colItems = Get-WmiObject -computername $row["SystemName"] -cred $cred Win32_Service -filter "Name LIKE '%SQL%'" -ea silentlycontinue

foreach ($objItem IN $colItems)
{
$SystemName = $row["SystemName"]
$DisplayName = $objItem.DisplayName
$StartName = $objItem.StartName
$StartMode = $objItem.StartMode
$State = $objItem.State

$insert_query = "INSERT INTO dbo.Server_Info
(SystemName, DisplayName, StartName, StartMode, State)
VALUES ('$SystemName', '$DisplayName', '$StartName', '$StartMode', '$State')"

$execute_query = New-Object System.Data.SqlClient.SqlCommand
$execute_query.connection = $sql_conn
$execute_query.commandtext = $insert_query
$execute_query.executenonquery() | out-null
}
}

## Close conection(s):
##
$sql_conn.close()
[/code]
July 2, 2007 3:50 PM
You need to sign in to comment on this blog

















<May 2007>
SuMoTuWeThFrSa
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789
DML Trigger Status Alerts
 When databases suddenly stop working, it can be for a number of different reasons. Human error plays a... Read more...

Dr Richard Hipp, Geek of the Week
 Simple-Talk's Geek of the Week is Dr Richard Hipp. His code is probably running on your PC, and running... Read more...

Message Hygiene in Exchange Server 2007
 Around four out of every five email messages are spam. Now that the nuisance threatens to engulf what... Read more...

Optimizing the Exchange Environment - Send us your Tips
 We are running a monthly Exchange top tips competition where you can win a $50 Amazon voucher. Read more...

How to Track Down Deadlocks Using SQL Server 2005 Profiler
 It is irritating, sometimes alarming, for the user to be confronted by the 'deadlock message' when a... Read more...