SQL query duplicating results

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 5 months ago.

  • Author
    Posts
  • #6055

    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

    }

You must be logged in to reply to this topic.