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.by DonJ at 2013-03-13 08:06:54cls
$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
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
}