Click here to monitor SSC

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
 

Forum vicodin buy vicodin online. said:

Vicodin.
May 31, 2009 7:54 PM
You need to sign in to comment on this blog
Latest articles
A first look at SQL Server 2012 Availability Group Wait Statistics
 If you are trouble-shooting an AlwaysOn Availability Group topology, a study of the wait statistics... Read more...

SQL Server Prefetch and Query Performance
 Prefetching can make a surprising difference to SQL Server query execution times where there is a high... Read more...

SSIS Basics: Setting Up Your Initial Package
 When working with databases, the use of SQL Server Integration Services (SSIS) is a skill that often... Read more...

Checking Out SQL Backup Pro 7’s New Automatic Backup Verification
 Wouldn't it be great to offload the daily chore of checking the integrity of your production... Read more...

Chuck Lathrope: DBA of the Day
 Chuck Lathrope was a finalist for the Exceptional DBA of the Year award in 2009. We contacted him to... Read more...