[Solved] Get the users associated with Database

by 


function SQL-Get-Logins
{
    <#
    .SYNOPSIS
    Returns a list of SQL Server logins defined on the specified server/instance.

    .DESCRIPTION
    This function returns a complete list of all logins defined for the specified
    server/instance, including the login type, the default database, and the server
    role.

    .PARAMETER server
    The computer hosting SQL Server.

    .PARAMETER instance
    The instance to be used on that server.

    .PARAMETER saPassword
    The 'sa' password on that server\instance

    .EXAMPLE
    SQL-Get-Logins myserver myinstance sa | %{ Write-Host $_.LoginName }
    #>

    param (
        [parameter(Mandatory = $true)][string] $server,
        [parameter(Mandatory = $false)][string] $instance="MSSQL",
        [parameter(Mandatory = $true)][string] $saPassword
    )

    $logins = $null

    try
    {
        $query = @"
        select a.name as LoginName, a.type_desc as LoginType, a.default_database_name as DefaultDBName,
        case when b.sysadmin = 1 THEN 'sysadmin'
                  when b.securityadmin=1 THEN 'securityadmin'
                  when b.serveradmin=1 THEN 'serveradmin'
                  when b.setupadmin=1 THEN 'setupadmin'
                  when b.processadmin=1 THEN 'processadmin'
                  when b.diskadmin=1 THEN 'diskadmin'
                  when b.dbcreator=1 THEN 'dbcreator'
                  when b.bulkadmin=1 THEN 'bulkadmin'
                  else 'Public' end as ServerRole
        from sys.server_principals a join master..syslogins b on a.sid=b.sid where a.type  <> 'R' and a.name not like '##%'
"@

        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $sqlConnection.ConnectionString = "Server=$server\$instance;Database=master;User Id=sa;Password=$saPassword;Trusted_Connection=False;"
        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter ($query, $sqlConnection)
        $dataSet = New-Object System.Data.Dataset
        $adapter.Fill($dataSet) | Out-Null 

        $logins = $dataSet.Tables[0]

        foreach ($login in $logins)
        {
            $loginName = $login.ItemArray[0]
            $loginType = $login.ItemArray[1]
            $defaultDbName = $login.ItemArray[2]
            $serverRole = $login.ItemArray[3]

            Write-Output "$($MyInvocation.InvocationName): LoginName: $loginName, LoginType: $loginType, Default database: $defaultDbName, Server Role: $serverRole"
        }
    }

    catch
    {
        Write-Error "$($MyInvocation.InvocationName): $_"
    }

    return $logins
}

1

solved Get the users associated with Database