Trevor: Best Wishes From Cleveland

Hi Trevor,
We are no place exotic, but we are thinking of you and wishing you a speedy recovery.
Here's a picture of us in our backyard in Cleveland, Ohio.

(Brian, Jenn, Tyler, Nate)

And another, warmer picture, from Devil's Lake, Michigan.

Hoping you feel better soon!
The Davis Family

TSQL Tuesday #15: Automation

Now that I've started blogging I decided to throw my hat into the ring for TSQL Tuesday, hosted this month by Pat Wright aka SQLAsylum (Blog | Twitter) and the topic is Automation.

Recently, I needed to compile a listing of all the accounts that were running all of the SQL Server services in the environment.  After throwing the manual option out the window and  looking at a few other options I decided that PowerShell would provide the best solution.  In my environment I have dedicated database that houses information I've collected about all of the servers.  One of the tables just happens to contain a list of servers.  Using PowerShell I'll scan through the list of servers and then run a WMI query against each to get the accounts for each SQL service on the server.  Once I have the accounts and their associated services I then upload that information to a table in my database.  From there the data can be exported or displayed via SSRS.

The code is pretty well documented so let's take a look at the script (download link at end of post).

# =======================================================================
#  SCRIPT BODY
# =======================================================================
#Initialize the SQL Environment
.\Initialize-SqlpsEnvironment.ps1

#Define Counter Variables
[int]$count=0
[int]$total=0

#Define the destination server and database names
$sqlsrv = "SQLSrv01"
$destdb = "SvcDB"

#Clean out the SvcInfo Table
$q = "delete LoadSvcInfo"
$reslist = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
Write-Host "Clean SvcInfo Table"

#Define the list server and database
$listsrv = "SQLSrv01"
$listdb = "ServerDB"

#Get server list
$q = "select distinct servername from ServerList order by servername"
$reslist = invoke-sqlcmd -ServerInstance $listsrv -Database $listdb -Query $q
$total = $reslist.length
foreach ($strSrv in $reslist){
#Increment Counter
$count++

#Display current server & count
Write-Host "Server: " $strSrv.ServerName " " $count " of " $total

#$m = New-Object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $strSrv.ServerName

#Get Service Account Info
$colItems = gwmi win32_service -computer $strSrv.ServerName -property name, startname, caption | where {$_.Name -like "*SQL*"}
foreach($objItem in $colItems) {

#Send info to our database
$q = "insert [SvcDB].[dbo].[SvcInfo] (SrvName, SvcName, SvcCaption, SvcAcct) Values ("
$q = $q + "'" + [string]$strSrv.ServerName + "', "
$q = $q + "'" + [string]$objItem.name + "', "
$q = $q + "'" + [string]$objItem.caption + "', "
$q = $q + "'" + [string]$objItem.startname + "')"
$res = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
}

$colItems = gwmi win32_service -computer $strSrv.ServerName -property name, startname, caption | where {$_.Name -like "MSDTSServer*"}
foreach($objItem in $colItems) {

#Send info to our database
$q = "insert [SvcDB].[dbo].[SvcInfo] (SrvName, SvcName, SvcCaption, SvcAcct) Values ("
$q = $q + "'" + [string]$strSrv.ServerName + "', "
$q = $q + "'" + [string]$objItem.name + "', "
$q = $q + "'" + [string]$objItem.caption + "', "
$q = $q + "'" + [string]$objItem.startname + "')"
$res = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
}
#$m = $null
}
Write-Host "DONE!"
#=======================================================================
#  END OF SCRIPT: [ServiceAccounts]
#=======================================================================

Download Zip of ServiceAccounts Script

SQL Saturday #60 - 2 Firsts in 1 Day

This coming Saturday (2/5/2011) the SQL user group I am a part of, Ohio North SQL Server User Group, will be hosting its' first SQL Saturday #60.  It's been a lot of fun helping to organize what I know will be a great event.  We've got speakers coming from all over along with many great local speakers and the session lineup is amazing.  If a day of free SQL Server training from some outstanding speakers isn't enough to get you to come to Cleveland in February, check out the Allen White's (Blog | Twitter) Top Ten Reasons to Come to Cleveland in February.  If that wasn't enough we also have a great after party planned at Wingz followed by SQL Karaoke at Grillers Pub.  Come join us for a great day of SQL Server, networking, and a lot of fun!

On a personal note, SQL Saturday #60 will be special for me for two reasons.  First, it will be the first SQL Saturday I've attended.  Second, it will be my first time presenting.  I'm presenting "Service Broker...What's That & How Do I Use It".  This will be a beginner level session where will take a look at what Service Broker is and how it works.  I'll walk through the setup and configuration along with some usage examples.  I'm really looking forward to presenting and getting my first presentation under my belt.  Hope to see you at SQL Saturday #60.

SQL Server Separation of Duties Framework 2.0 Released!

Last November at PASS I attended a session entitled "Si Se Puede! Achieving Separation of Duties with SQL Server" presented by Il-Sung Lee and Lara Rubbelke (blog | twitter).  Lara and Il-Sung discussed the separation of duties between the DBA and the sysadmin role and presented the SQL Server Separation of Duties Framework to help facilitate this.
Version 2.0 of the Separation of Duties Framework has just been released and it can help you create a controlled environment to manage permissions for various levels of DBA's and/or users.

Do you ever need to grant a user specific elevated permissions?  
What about granting some junior level DBA's the ability to unlock a login but not the ability to alter it?

Using the SoD Framework you can setup multiple roles with specific permissions and assign users to them as necessary.  This framework can help accomplish these tasks easily and in a controlled environment.  It's completely user definable and as of this release is capable of handling multiple tiers of users.  Check out
Lara's latest blog post for more information.  Check it out and leave us a comment if you have any questions or issues.

It's been a great experience working with Lara Rubbelke (blog | twitter) on version 2.0 of this framework and we are looking forward to continuing the development of this project.

New Year, New Resolutions

It's a new year...

• I'll be married for 9 years in April
• My oldest son will be 7 in November
• My youngest son will be 4 in July
• I'll be speaking at my first SQL Saturday in February (SQL Saturday #60 in Cleveland) and hopefully more afterwards
• I'll have been at my current job for 4 years in September

New Resolutions

With a new year comes new resolutions.  In the past I haven't always stuck to my resolutions but this year, more than any other, I plan to.  These resolutions are important me and will help get me to where I think I want to be in life, so this year, more than any other in the past, I plan to accomplish them.  So, without further to do, in no particular order, here are my resolutions for 2011.

• Blog at lest once a month
• Get out and run at least twice a week
• Contribute as much as I can back to the SQL community that has given me so much
• Continue to be involved with the Ohio North SQL Server User's Group
• Be involved in my kids lives and have fun with them

Even though it's a bit late, Happy New Year to all.  I hope you have a great year!

Hello Blogosphere - Who I am & Why I'm Here

Short BIO
My name is Brian Davis and I've been in the IT field for almost 15 years.  I started as a developer working with FoxPro 2.6 for DOS/VFP 6/VB6, moving up through VFP7/8/9.  In 2003 the company I worked for asked me if I'd be interested in doing double duty as a developer and DBA.  I said yes and started working with SQL Server 2000 and never looked back.  Since then I've changed companies and became a full time database analyst.  I'm currently one of a handful of DBA's responsible for maintaining servers running everything from SQL 2000 to 2008R2.  I've also become involved with the SQL User Group here in Cleveland, OH.

Why Blog?
After being inspired by many people in the SQL Server community I've decided to start writing my own blog.  I've found more helpful tips and tricks than I can remember from blogs written by generous members of the SQL community.  My hope is that by blogging about my own experiences that I will be able to give something back to community that has helped me so much.

What to Expect Here?
My plan is to post about my experiences with SQL Server.  The pitfalls I encounter and the lessons I learn, along with any scripts/code that I've created or found useful.  I've already got a few blog posts lined up and will be working on them in the days and weeks ahead.  Along with that I've also been working on a plug-in for SSMS that I am planning to release.

Thank You!
Finally, I want to say thank you to the SQL community for all the support and information you've shared with me.  Without the community I wouldn't be where I am today.