Archive for the ‘T-SQL Tuesday’ Category

T-SQL Tuesday #39 – Set file permissions with PowerShell

12/02/2013 1 comment


Wayne Sheffield (blog|twitter) is hosting this month’s T-SQL Tuesday and the subject is “Can you shell what the PoSH is Cooking“. More specifically he want that we blog about anything PowerShell and SQL Server related.

As a DBA I use PowerShell for some automation tasks such as: reading error logs, scripting SQL server objects, comparing data in tables etc. Today I will write about script I wrote for setting read file permission on SQL Server trace files.

From SQL Server 2005 onwards, when you run server side trace in SQL server, a new trace file will be created and the permission for this trace file is set only to SQL Service account. The permissions of the directory are not inherited.

Because of that SQL Server security policy, I find myself in a situations that I have hundreds of trace files on the server share, but I cannot access them with my user. So I decided to write a PowerShell script to give my user read access to all trace files in specified folder.

$path = "D:\Folder\"
$user = "domain\username"
$SystemRights = "Read"
$AccessType = "Allow"
$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, $SystemRights, $AccessType)
$files = Get-ChildItem $path
foreach($file in $files)
$acl = (Get-Item $file).GetAccessControl("Access")
$filepath = $path +""+ $
Set-Acl $filepath $acl
Categories: T-SQL Tuesday

T-SQL Tuesday #38 – Standing Firm

08/01/2013 Leave a comment


Today’s T-SQL Tuesday is hosted by Jason Brimhall (blog|twitter). The topic this month is “Standing Firm”. This means that I should write something related to one of these words: resolve, resolution, or resolute.

It’s the time of year when many people make New Year’s resolutions and this is the first time I will make a three related to SQL Server:  to write more blog posts about SQL Server,  to do more local community speaking about SQL Server and to learn more about AlwaysOn, Performance Tuning and PowerShell.

Before a year and a half I started with writing a blog which is related to SQL Server and till now I wrote 28 posts. But only 5 of them I wrote last year.  I started to write a few blog posts last year but I never finished them.  Topics I’ve chosen for these posts were very complex and I never had time to finish them. Maybe it’s time to try a different tactic – writing about simple topics or writing more posts about complex topic (like blog post series).

I spoke once in local MS Community about SQL Server Policy Based Management and had very positive feedback. That was excellent experience and I want to more actively participate in our local user group. I will try to speak at least 2 times this year.

I always try to learn something new and this time I want to go further with AlwaysOn and Performance Tuning areas of SQL Server. I used PowerShell several times and I can say that it can be a powerful tool in the hands of DBA. I can even try to write a blog post series on these topics.

With this post I made a lot of firsts: the first blog post this year, the first time I am participating in T-SQL Tuesday and the first time I made SQL Server resolution. 🙂

Categories: T-SQL Tuesday