如何通过C#的Microsoft.SqlServer.Management.Smo.Server高效获取SQL Server数据库用户？

嘿，我之前也碰到过Smo默认遍历数据库用户/所有者时性能拉胯的情况，给你几个亲测有效的优化思路和替代方案：

Smo默认会采用延迟加载，而且会加载对象的所有属性，这在数据库数量多的时候会导致大量的数据库往返请求，拖慢性能。你可以通过SetDefaultInitFields方法指定只加载你需要的属性，避免不必要的数据传输。

比如获取数据库所有者的示例：

// 提前设置只加载Database对象的Name和Owner属性
m_server.ConnectionContext.SetDefaultInitFields(typeof(Database), "Name", "Owner");

foreach (Database db in m_server.Databases)
{
    Console.WriteLine($"数据库: {db.Name}, 所有者: {db.Owner}");
}

如果要获取每个数据库的用户列表，同样可以限制User对象的加载字段：

// 先限制Database只加载Name属性
m_server.ConnectionContext.SetDefaultInitFields(typeof(Database), "Name");
// 再限制User只加载Name和Type属性
m_server.ConnectionContext.SetDefaultInitFields(typeof(User), "Name", "Type");

foreach (Database db in m_server.Databases)
{
    Console.WriteLine($"=== 数据库: {db.Name} ===");
    foreach (User user in db.Users)
    {
        Console.WriteLine($"用户: {user.Name} ({user.Type})");
    }
}

如果追求极致性能，直接绕过Smo的封装，通过ConnectionContext执行原生T-SQL查询系统视图是最好的选择——毕竟Smo底层也是通过查询系统视图实现的，但它会做很多额外的封装工作。

获取所有数据库及所有者 #

using (var cmd = m_server.ConnectionContext.CreateCommand())
{
    cmd.CommandText = @"
        SELECT 
            d.name AS DatabaseName,
            sp.name AS OwnerName
        FROM sys.databases d
        INNER JOIN sys.server_principals sp 
            ON d.owner_sid = sp.sid
        WHERE d.state = 0; -- 只筛选在线状态的数据库";
    
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            string dbName = reader["DatabaseName"].ToString();
            string ownerName = reader["OwnerName"].ToString();
            Console.WriteLine($"数据库: {dbName}, 所有者: {ownerName}");
        }
    }
}

获取所有数据库的用户列表 #

using (var cmd = m_server.ConnectionContext.CreateCommand())
{
    cmd.CommandText = @"
        DECLARE @dbName NVARCHAR(128);
        DECLARE dbCursor CURSOR FOR 
            SELECT name FROM sys.databases WHERE state = 0;
        
        OPEN dbCursor;
        FETCH NEXT FROM dbCursor INTO @dbName;
        
        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- 动态查询每个数据库的用户（排除系统内置的主体）
            EXECUTE('
                SELECT 
                    ''' + @dbName + ''' AS DatabaseName,
                    dp.name AS UserName,
                    dp.type_desc AS UserType
                FROM ' + QUOTENAME(@dbName) + '.sys.database_principals dp
                WHERE dp.type NOT IN (''S'', ''C'', ''K'', ''R'') -- 排除系统用户、证书、密钥、角色
                AND dp.name NOT LIKE ''##%##''; -- 排除特殊系统账户
            ');
            
            FETCH NEXT FROM dbCursor INTO @dbName;
        END;
        
        CLOSE dbCursor;
        DEALLOCATE dbCursor;";
    
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            string dbName = reader["DatabaseName"].ToString();
            string userName = reader["UserName"].ToString();
            string userType = reader["UserType"].ToString();
            Console.WriteLine($"数据库: {dbName}, 用户: {userName} ({userType})");
        }
    }
}

Smo提供了EnumDatabases这类枚举方法，它们比直接遍历Databases集合更轻量，因为只会返回你指定的属性，不会创建完整的Database对象实例。

示例：

// 枚举数据库，只获取Name和Owner列
DataTable dbTable = m_server.EnumDatabases("Name", "Owner");

foreach (DataRow row in dbTable.Rows)
{
    Console.WriteLine($"数据库: {row["Name"]}, 所有者: {row["Owner"]}");
}

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