Author Posts

January 1, 2012 at 12:00 am

by bdizzle at 2013-03-12 09:21:46

I have a PS script that queries a DB for the permissions that are applied to it, and writes it to a file. For some reason, the results are duplicated from the query (if only 5 records are supposed to be returned, 10 is returned in the script output). If anyone can provide any help I'd appreciate it.

cls
$allPerms = @()
#Connect to SQL Server
$SQLServer = "SERVER"
$SQLDBName = "DATABASE"
$SqlQuery =
"
SELECT [UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = NULL,
[PermissionState] = perm.[state_desc],
[PermissionType] = perm.[permission_name],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM --database user
sys.database_principals princ
LEFT JOIN --Login accounts
sys.server_principals ulogin
ON princ.[sid] = ulogin.[sid]
LEFT JOIN --Permissions
sys.database_permissions perm
ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN --Table columns
sys.columns col
ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.objects obj
ON perm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas schem
ON schem.[schema_id] = perm.[major_id]
LEFT JOIN sys.database_principals imp
ON imp.[principal_id] = perm.[major_id]
WHERE princ.[type] IN ('S', 'U', 'G')
AND -- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
AND ulogin.[name] = 'pwdcour'
ORDER BY
ulogin.[name],
[UserType],
[DatabaseUserName],
[Role],
[PermissionState],
[PermissionType],
[ObjectType],
[ObjectName],
[ColumnName]
"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

if ($SqlAdapter.Fill($DataSet) -eq 0) { #Notifies that No accounts were created

}
ForEach ($row in $DataSet.Tables[0].Rows) {
$perm = New-Object PSObject
$perm | Add-Member NoteProperty -Name "UserName" -Value $row.UserName
$perm | Add-Member NoteProperty -Name "UserType" -Value $row.UserType
$perm | Add-Member NoteProperty -Name "DatabaseUserName" -Value $row.DatabaseUserName
$perm | Add-Member NoteProperty -Name "Role" -Value $row.Role
$perm | Add-Member NoteProperty -Name "PermissionType" -Value $row.PermissionType
$perm | Add-Member NoteProperty -Name "PermissionState" -Value $row.PermissionState
$perm | Add-Member NoteProperty -Name "ObjectType" -Value $row.ObjectType
$perm | Add-Member NoteProperty -Name "ObjectName" -Value $row.ObjectName
$perm | Add-Member NoteProperty -Name "ColumnName" -Value $row.ColumnName
$allPerms += $perm
}
}

$allPerms | Export-Csv C:\logs\sql_perms.csv -NoTypeInformation

by DonJ at 2013-03-13 08:06:54

Consider adding some debugging code here. Like, how many times is your ForEach actually executing? 5 or 10? What exactly is in the data set prior to the ForEach – 5 rows, or 10 rows? In other words, where is the duplication occurring – coming from SQL (bad query) or in PowerShell (bad loop)?

by poshoholic at 2013-03-18 08:26:37

At a glance, this part of your script seems to be the culprit:
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

if ($SqlAdapter.Fill($DataSet) -eq 0) { #Notifies that No accounts were created

}
You're calling Fill twice: once to fill the dataset and a second time to check the result. Doesn't that second call also fill the dataset? Instead, you should try it like this:
$count = $SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

if ($count -eq 0) { #Notifies that No accounts were created

}