PowerShell - Get Permissions

tomeaton12 (5/18/2012)

Hello,

I am new to Powershell and I have been asked to get the SQL Server Database data path and insert this veriable into a script to gather permissions from the folders and inserts them into a csv file. I have insert my code so far below

Any help would be much appriciated.

Param (
[STRING] $SQLSERVER = "Server, port"
)
$permsLogFile = "C:\Users\Output.txt"
Write-Output "Start of the script" > $permsLogFile

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT SUBSTRING(filename,1,CHARINDEX('A\master.',filename)) from master..sysdatabases WHERE name = 'master'"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$networkPath = $DataSet.Tables[0]
$subFolders = Get-ChildItem -Name $networkPath
foreach ($Folder in $subFolders){
$cmd = "cacls $networkPath\$Folder"
Write-Output "Running Commamnd:" $networkPath >> $permsLogFile
Invoke-Expression $cmd | Write-Output >> $permsLogFile}
$SqlConnection.Close()

$sName = "mysvr"
$dbName = "mydb"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($sName)
$db = $server.Databases[$dbName]

Get-ChildItem $db.PrimaryFilePath -Recurse | Where-Object{($_.psiscontainer)} | Get-Acl

# ..

$sName = 'mysrr'
$server = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $sName
foreach ($db in $server.Databases) {
$db.name
$fg = $db.FileGroups
foreach ($group in $fg) {
$group.Name
foreach ($file in $group.Files) {
$file.FileName
}
}

foreach ($log in $db.LogFiles) {
$log.FileName
}
}

Add-Type -AssemblyName Microsoft.SqlServer.Smo
$instanceName = '.\STANDARD2008R2'
$filePaths = @()
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
foreach ($db in $server.Databases) {
#$db.name
$fg = $db.FileGroups
foreach ($group in $fg) {
#$group.Name
foreach ($file in $group.Files) {
$filePaths += (Split-Path -Path $file.FileName -Parent)
}
}

foreach ($log in $db.LogFiles) {
$filePaths += (Split-Path -Path $log.FileName -Parent)
}
}
$filePaths = $filePaths | Select-Object -Unique

# array now contains unique list of paths where data or log files exist
$filePaths

$server = "svr"
$sqlserver = new-object “Microsoft.SqlServer.Management.Smo.Server” $server
Invoke-Sqlcmd -Query "SELECT a.name ,
b.filename
FROM sys.sysdatabases a
INNER JOIN sys.sysaltfiles b ON a.dbid = b.dbid
WHERE fileid <> 2
ORDER BY name ;" -ServerInstance $sqlserver