Microsoft Dynamics NAV 2009及更早版本如何编程获取所有用户？

Great question! For Dynamics NAV 2009 and earlier versions where the convenient Get-NAVServerUser cmdlet isn't available, you have two solid approaches to programmatically retrieve all users—using PowerShell or C#. Let's walk through each method with actionable examples:

You can either query the underlying SQL database directly or leverage the NAV Classic Client's automation object, depending on your setup and permissions.

Option 1: Query the SQL Database Directly #

NAV 2009 stores user data in the User table of your company's SQL database. This is the most straightforward method if you have SQL access:

# Replace these placeholders with your actual SQL instance and NAV database name
$sqlInstance = "YOUR_SQL_SERVER\INSTANCE_NAME"
$navDatabase = "NAV_COMPANY_DATABASE"

# Fetch user details with a SQL query
Invoke-SqlCmd -ServerInstance $sqlInstance -Database $navDatabase -Query @"
SELECT [User ID], [Full Name], [State], [Expiry Date]
FROM [dbo].[User]
"@ | Format-Table -AutoSize

Note: Ensure the account running the script has read permissions on the NAV SQL database. If your deployment uses a non-default schema, adjust the table reference accordingly.

Option 2: Use the NAV Classic Client Automation Object #

If you have the NAV Classic Client installed, you can automate it to pull user data just like the client would:

# Initialize the NAV Classic Client COM object
$navClient = New-Object -ComObject "Microsoft.Dynamics.Nav.Client"

# Connect to your NAV server and company
$navClient.OpenConnection("YOUR_NAV_SERVER_ADDRESS", "YOUR_COMPANY_NAME")

# Retrieve and loop through all user records
$userRecord = $navClient.OpenRecord("User")
$userRecord.FindSet()

do {
    [PSCustomObject]@{
        'User ID' = $userRecord.GetFieldValue("User ID")
        'Full Name' = $userRecord.GetFieldValue("Full Name")
        'Account State' = $userRecord.GetFieldValue("State")
    }
} while ($userRecord.Next())

# Clean up resources
$navClient.CloseConnection()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($navClient) | Out-Null

Warning: This method requires the Classic Client to be installed on the machine running the script, and your user account needs NAV permissions to access the User table.

For more robust integrations, C# offers two reliable paths: direct SQL queries or the NAV .NET Business Connector.

Option 1: ADO.NET SQL Query #

Connect directly to the NAV SQL database using standard ADO.NET tools:

using System;
using System.Data.SqlClient;

class NavUserFetcher
{
    static void Main()
    {
        string connectionString = @"Server=YOUR_SQL_SERVER\INSTANCE;Database=NAV_COMPANY_DATABASE;Integrated Security=True";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            
            string query = @"SELECT [User ID], [Full Name], [State], [Expiry Date] FROM [dbo].[User]";
            using (SqlCommand command = new SqlCommand(query, connection))
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"User ID: {reader["User ID"]}, Full Name: {reader["Full Name"]}, State: {reader["State"]}");
                }
            }
        }
    }
}

Option 2: NAV .NET Business Connector #

Use the official .NET Business Connector (included with NAV 2009 server/development tools) to interact with NAV's business logic directly:

using System;
using Microsoft.Dynamics.Nav.BusinessConnector;

class NavUserRetriever
{
    static void Main()
    {
        NavSession navSession = new NavSession();
        
        try
        {
            // Connect to NAV with Windows authentication
            navSession.Open("YOUR_NAV_SERVER_ADDRESS", "YOUR_COMPANY_NAME", null, null, NavAuthenticationType.Windows);
            
            // Open the User table and fetch all records
            NavRecord userRecord = navSession.OpenRecord("User");
            userRecord.FindSet(false, false);
            
            do
            {
                string userId = userRecord.GetFieldValue("User ID").ToString();
                string fullName = userRecord.GetFieldValue("Full Name").ToString();
                string state = userRecord.GetFieldValue("State").ToString();
                
                Console.WriteLine($"User ID: {userId}, Full Name: {fullName}, State: {state}");
            } while (userRecord.Next() == NavRecordStatus.Valid);
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error retrieving users: {ex.Message}");
        }
        finally
        {
            // Clean up the session
            if (navSession.IsOpen)
                navSession.Close();
            navSession.Dispose();
        }
    }
}

Note: You'll need to reference Microsoft.Dynamics.Nav.BusinessConnector.dll in your project, and the running user must have appropriate NAV permissions to access user data.

内容的提问来源于stack exchange，提问作者Miljan